Hello,
I am trying to do the following:
- create a form (let’s call it FormVars) to capture the values of a number of variables that I don’t know in advance
- create an API (let’s call it SaveVars) to save to a DB table all the values captured using FormVars
I have 3 questions about this:
- how to create the form FormVars;
- how to pass the captured data to the SaveVars API; and
- how to parse in the SaveVars API the data passed to it.
The possible variables that could be captured and the initial values to present to the user are defined in a table --let’s call this table with the definitions of the variables VARDEF.
I want to store the values captured through FormVars in a separate table --let’s call this table with the entered values of those variables VARSAV.
Below is a simplified structure the VARDEF table, the one that contains the definitions of the variables to capture:
VarDefID (PK)
....
Name (a string that defines the parameter)
Label (a string that is user friendly and can be used in a form)
Default (initial value shown to the user in the FormVars)
The VARDEF could have a large number of records, and I have no control over what is kept there. At a given point in time, the table could look like this:
VarDefID Name Default Label
---------------------------------------------------
103 NumBdrm 2 Number of Bedrooms
...
157 NumBath 2.5 Number of Bathrooms
...
197 HasDen N Unit has a den
...
1123 BYrdPool Y Backyard has pool
Below is a simplified structure the VARSAV table, the one that contains the values of the variables entered by the user
VarSavID (PK)
…
VarDefID (FK)
EnteredValue (the value entered by the user using the form FormVars)
I already wrote a query (GetVars) that fetches from VARDEF the values that need to be entered using FormVars. Assume that for one particular case, GetVars returned the records 103, 157, and 197 from the VARDEF table.
These 3 variables should be presented to the user in FormVars. After the user entered the new values of these three variables, I’d like the SaveVars API to insert the 3 records in VARSAV, which may end up looking like this:
VarSavID VarDefID EnteredValue
1154 103 3
1155 157 2
1156 197 Y
The numbers shown for the Primary Keys in both tables are irrelevant, as they are only used for referencing the rows in this question.
Question1:
What is the best approach to creating FormVars, so it can present to the user all the variables for which the user needs to enter values? In this example, FormVars should present 3 input widgets for NumBdrm, NumBath, and HasDen but in other cases, it may have to deal with a completely different set of variables.
Question 2:
What structure should I use to submit the values captured by FormVars, to pass them to the API SaveVars? I thought of something like an array of (name, value) pairs, probably something like an array of (VarDefID, EnteredValue) pairs, but I’m not a programmer and I don’t know how to assemble such array in the page so the submit button can pass it.
Question 3:
Assuming that passing an array of (name, value) pairs to the SaveVars API is indeed the right way to go, how do I parse that array so I can save its contents in the VARSAV table? I think I need to iterate through that array in a repeat loop, which I know how to do if the array is the result of a database query, but I don’t know in this case.
Any guidance on these questions will be greatly appreciated!
Many thanks in advance,
Alex
Last updated: