Dynamic list field mapped to table column not working
Moderators: Jon, Steve, Ian, Dave
-
- Ebase User
- Posts: 649
- Joined: Mon Dec 09, 2013 6:37 pm
Dynamic list field mapped to table column not working
I am running into an issue with a dynamic list and I am not sure if this is a limitation with Ebase or if I'm doing something wrong.
I am creating a dynamic page that displays options based on option groups and option values.
The option group table table has a unique option group ID called Opt_GroupID and a descriptive label called Opt_Group_Desc.
The option values are defined in a table called PCS_Option_Group_Values which has 2 columns Opt_Group_OptionID for the option values' unique ID and Opt_Group_Desc.
The assignments are done in a table called PCS_Option_Group_Value_Assignments which references the option group ID (Opt_GroupID) and the option value ID (Opt_Group_OptionID).
I have a dynamic list that I use to populate a dropdown with values based on a option group ID so for example option group ID 1 may have 2 values defined in the database with the values Yes and No so the dropdown will have these 2 values when the option group id is 1.
The dynamic list looks like this:
Select columns: *
Select from tables:
PCS_Option_Group_Value_Assignments LEFT JOIN PCS_Option_Group_Values ON PCS_Option_Group_Values.Opt_Group_OptionID=PCS_Option_Group_Value_Assignments.Opt_Group_OptionID
Where clause:
Opt_GroupID=&&Opt_GroupID
Additional SQL statement clause:
ORDER BY Opt_Group_Display_Sequence
with 3 list fields (Opt_GroupID,Opt_Group_Desc,Opt_Group_OptionID)
I have a form that uses this dynamic list to dynamically create multiple dropdowns using a repeater. The repeater returns multiple option group IDs. For each row which has the option group ID, I use a field which is displayed as a dropdown in the repeater. The group ID in the repeater is linked to the group ID in the fields' dynamic list mapping. The gives me a dropdown with the values for that specific option group and this works perfectly.
On a separate page, I have a table which lets you manage all of the option groups. The first column is the option group ID and the last column is a default value column (which is an integer) which is the id of the default option value for that option group.
I want to be able to link the current rows' option group ID (in column 1) to this dynamic list so the dropdown will only contain values for the current option group ID based on the ID in the 1st column of the table.
I mapped the Opt_GroupID in the dynamic list to the table group ID (which is supposed to be the group ID for that particular row) so I can select the default value but what I end up with is the same value for all rows which in this case is yes or no which I think is based on the option group ID of the first row which in fact does only have yes or no values instead of the current rows' group ID.
What am I doing wrong here ?
Update: As a test, I created a 2nd dynamic list for the option management group page defined like this:
Select columns: *
Select from tables: PCS_Option_Group_Values
Additional SQL: ORDER BY CASE WHEN ISNUMERIC(Opt_Group_Desc)=1 THEN 0 ELSE 1 END,Opt_Group_Desc
As you can see, this 2nd dynamic list doesn't filter by the option group ID and sort of works correctly. When using this dynamic list, the default value which show all values and not just ones specific to the current option group ID
I am creating a dynamic page that displays options based on option groups and option values.
The option group table table has a unique option group ID called Opt_GroupID and a descriptive label called Opt_Group_Desc.
The option values are defined in a table called PCS_Option_Group_Values which has 2 columns Opt_Group_OptionID for the option values' unique ID and Opt_Group_Desc.
The assignments are done in a table called PCS_Option_Group_Value_Assignments which references the option group ID (Opt_GroupID) and the option value ID (Opt_Group_OptionID).
I have a dynamic list that I use to populate a dropdown with values based on a option group ID so for example option group ID 1 may have 2 values defined in the database with the values Yes and No so the dropdown will have these 2 values when the option group id is 1.
The dynamic list looks like this:
Select columns: *
Select from tables:
PCS_Option_Group_Value_Assignments LEFT JOIN PCS_Option_Group_Values ON PCS_Option_Group_Values.Opt_Group_OptionID=PCS_Option_Group_Value_Assignments.Opt_Group_OptionID
Where clause:
Opt_GroupID=&&Opt_GroupID
Additional SQL statement clause:
ORDER BY Opt_Group_Display_Sequence
with 3 list fields (Opt_GroupID,Opt_Group_Desc,Opt_Group_OptionID)
I have a form that uses this dynamic list to dynamically create multiple dropdowns using a repeater. The repeater returns multiple option group IDs. For each row which has the option group ID, I use a field which is displayed as a dropdown in the repeater. The group ID in the repeater is linked to the group ID in the fields' dynamic list mapping. The gives me a dropdown with the values for that specific option group and this works perfectly.
On a separate page, I have a table which lets you manage all of the option groups. The first column is the option group ID and the last column is a default value column (which is an integer) which is the id of the default option value for that option group.
I want to be able to link the current rows' option group ID (in column 1) to this dynamic list so the dropdown will only contain values for the current option group ID based on the ID in the 1st column of the table.
I mapped the Opt_GroupID in the dynamic list to the table group ID (which is supposed to be the group ID for that particular row) so I can select the default value but what I end up with is the same value for all rows which in this case is yes or no which I think is based on the option group ID of the first row which in fact does only have yes or no values instead of the current rows' group ID.
What am I doing wrong here ?
Update: As a test, I created a 2nd dynamic list for the option management group page defined like this:
Select columns: *
Select from tables: PCS_Option_Group_Values
Additional SQL: ORDER BY CASE WHEN ISNUMERIC(Opt_Group_Desc)=1 THEN 0 ELSE 1 END,Opt_Group_Desc
As you can see, this 2nd dynamic list doesn't filter by the option group ID and sort of works correctly. When using this dynamic list, the default value which show all values and not just ones specific to the current option group ID
0 x
-
- Moderator
- Posts: 184
- Joined: Tue Sep 11, 2007 8:58 am
-
- Ebase User
- Posts: 649
- Joined: Mon Dec 09, 2013 6:37 pm
Hovik,
It seems that Ebase does not currently support what I want to do .
Each table row has an option group ID which may be the same or different per table row (in other words, the option group Id may be duplicated across different rows but this is normal). However, the dynamic list (which is set to each display and is mapped to the current rows' option group ID) is only being mapped to the option group ID of the first row. I turned on debugging for the dynamic list and confirmed that it only executes with the first option group ID that it encounters.
If this is not the expected behavior, then its probably a bug in Ebase.
It seems that Ebase does not currently support what I want to do .
Each table row has an option group ID which may be the same or different per table row (in other words, the option group Id may be duplicated across different rows but this is normal). However, the dynamic list (which is set to each display and is mapped to the current rows' option group ID) is only being mapped to the option group ID of the first row. I turned on debugging for the dynamic list and confirmed that it only executes with the first option group ID that it encounters.
If this is not the expected behavior, then its probably a bug in Ebase.
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
There is some optimization code in Ebase which is trying to make sure that a Dynamic List is not built unnecessarily when it's displayed in a table column and it sounds as if you have activated this. To get the list to build for every row the list must contain at least one mapping to another column within the same table e.g.
Col T1-A is a group category id (doesn't matter whether this also has a Dynamic List)
Col T1-B is a list of groups within the category displayed as a dropdown Dynamic List
Col T1-B refers to col T1-A in its dynamic list field mappings (this is then fed into the WHERE clause)
I don't think I've fully understood your scenario, but hopefully this is enough info for you to sort it out. And yes, Ebase does support the idea of rebuilding a list for each row within a table - but it won't do it if it thinks the list will be identical for each row.
If you have the correct mappings set up and it doesn't work then it's a bug. The only other thing that I can see that might stop it working is if any of the controls were originally hidden e.g. the column with the list or its Repeater parent or any of the Repeater's parents.
Col T1-A is a group category id (doesn't matter whether this also has a Dynamic List)
Col T1-B is a list of groups within the category displayed as a dropdown Dynamic List
Col T1-B refers to col T1-A in its dynamic list field mappings (this is then fed into the WHERE clause)
I don't think I've fully understood your scenario, but hopefully this is enough info for you to sort it out. And yes, Ebase does support the idea of rebuilding a list for each row within a table - but it won't do it if it thinks the list will be identical for each row.
If you have the correct mappings set up and it doesn't work then it's a bug. The only other thing that I can see that might stop it working is if any of the controls were originally hidden e.g. the column with the list or its Repeater parent or any of the Repeater's parents.
0 x
-
- Ebase User
- Posts: 649
- Joined: Mon Dec 09, 2013 6:37 pm
- dvanhussel
- Ebase User
- Posts: 161
- Joined: Fri Oct 19, 2007 12:45 pm
- Location: Haarlem, the Netherlands
I too think that this is a bug because I have seen the same behaviour when a customList is used: A dropdown in the first row of a repeater is populated correctly, in the folowwing rows it is empty.Segi wrote:Hovik,
When I use this same dynamic list with a non-table row, it works perfectly. I have double and triple checked the mapping and they are correct. I can only assume that its either a bug or the optimization that is preventing it from recreating for each row.
I have 'fixed' this by calling the function that creates the customList for every repeater row.
0 x
-
- Ebase User
- Posts: 649
- Joined: Mon Dec 09, 2013 6:37 pm
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
We've not been able to reproduce this problem yet. If you could send an export of the form (or just a test form showing the problem) to support@ebasetech.com, we'll have a look.
0 x
-
- Ebase User
- Posts: 649
- Joined: Mon Dec 09, 2013 6:37 pm
Jon,
I emailed you guys an example that I put together that demonstrates this bug. I created 2 tables in SQL Server:
ReferenceID
--------------
ID INT
and added the values 1,2 and 3 to this table
ReferenceID2
----------------
ReferenceID INT
ReferenceValue VARCHAR(80)
and added a total of 9 rows with these values:
1 Choice 1
1 Choice 2
1 Choice 3
2 Choice 4
2 Choice 5
2 Choice 6
3 Choice 7
3 Choice 8
3 Choice 9
When you run the form, I would expect that the dropdown on the first row (which is ID 1) would contain Choice 1, Choice 2 and Choice 3, the dropdown on row 2 would contain Choice 4, Choice 5 and Choice 6 and the dropdown on row 3 would contain Choice 7, Choice 8 and Choice 9 but all 3 dropdowns contain the same 3 values which are Choice 1, Choice 2 and Choice 3 indicating that the dropdown list is built off of the first ID only.
I forgot to set the dropdown to Each Display before sending you guys the example (which is what I have the dropdown set to in my application) but this doesn't affect anything.
I emailed you guys an example that I put together that demonstrates this bug. I created 2 tables in SQL Server:
ReferenceID
--------------
ID INT
and added the values 1,2 and 3 to this table
ReferenceID2
----------------
ReferenceID INT
ReferenceValue VARCHAR(80)
and added a total of 9 rows with these values:
1 Choice 1
1 Choice 2
1 Choice 3
2 Choice 4
2 Choice 5
2 Choice 6
3 Choice 7
3 Choice 8
3 Choice 9
When you run the form, I would expect that the dropdown on the first row (which is ID 1) would contain Choice 1, Choice 2 and Choice 3, the dropdown on row 2 would contain Choice 4, Choice 5 and Choice 6 and the dropdown on row 3 would contain Choice 7, Choice 8 and Choice 9 but all 3 dropdowns contain the same 3 values which are Choice 1, Choice 2 and Choice 3 indicating that the dropdown list is built off of the first ID only.
I forgot to set the dropdown to Each Display before sending you guys the example (which is what I have the dropdown set to in my application) but this doesn't affect anything.
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
Resolved offline. Conclusion is that to get a Dynamic List to build separately for each row in a table, two things are required:
- o there must be an additional dynamic list mapping that references another column in the same table (though this need not be displayed)
o this additional mapping must be marked as required in the Dynamic List
0 x
- dvanhussel
- Ebase User
- Posts: 161
- Joined: Fri Oct 19, 2007 12:45 pm
- Location: Haarlem, the Netherlands
Hi Jon,
Can you please have a look at the example that I created?
https://onedrive.live.com/redir?resid=5 ... file%2czip
In this Example I don't use a Dynamic List but a CustomList. I can't figure out how I could create the extra mapping that is required.
In this example I expect both 'Itemtype' fields to contain a dropdown. But only the one in the last tablerow does.
If I loop through the parent table and create the dropdown for each row, the dropdown is pressent in every repaterRow. But I think this should not be needed.
Regards,
David
Can you please have a look at the example that I created?
https://onedrive.live.com/redir?resid=5 ... file%2czip
In this Example I don't use a Dynamic List but a CustomList. I can't figure out how I could create the extra mapping that is required.
In this example I expect both 'Itemtype' fields to contain a dropdown. But only the one in the last tablerow does.
If I loop through the parent table and create the dropdown for each row, the dropdown is pressent in every repaterRow. But I think this should not be needed.
Regards,
David
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
David,
The need to set up list mappings applies only to Dynamic Lists, not other list types. I looked at your script - the problem is with the positioning of statement:
This creates a list and attaches it to the itemtype column on the current row of the items table which in your case is the last row as you've just issued some insertRow statements. So when data exists in your table, createCustomList() creates a private list just for one column on one row. This allows you to set different lists on each row if you want to.
But if the table is empty when you issue createCustomList() this then creates a list that will be shared by all rows in the table i.e. it acts as a default.
So you can solve your problem by moving the list creation before the items table inserts - i.e. when the items table is empty.
Regards
Jon
The need to set up list mappings applies only to Dynamic Lists, not other list types. I looked at your script - the problem is with the positioning of statement:
Code: Select all
var itemTypeList = items.itemtype.createCustomList();
But if the table is empty when you issue createCustomList() this then creates a list that will be shared by all rows in the table i.e. it acts as a default.
So you can solve your problem by moving the list creation before the items table inserts - i.e. when the items table is empty.
Regards
Jon
0 x
- dvanhussel
- Ebase User
- Posts: 161
- Joined: Fri Oct 19, 2007 12:45 pm
- Location: Haarlem, the Netherlands
-
- Ebase User
- Posts: 86
- Joined: Thu Mar 08, 2018 2:52 pm
Re:
I have a similar issue and can't get this working in a repeater. I thought I followed yr instruction but must be doing something wrong. Is there an example source available?Jon wrote: ↑Thu Jun 02, 2016 8:08 amResolved offline. Conclusion is that to get a Dynamic List to build separately for each row in a table, two things are required:
- o there must be an additional dynamic list mapping that references another column in the same table (though this need not be displayed)
o this additional mapping must be marked as required in the Dynamic List
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
Re: Dynamic list field mapped to table column not working
I'm not sure there's an example that shows everything. You might try the Introduction to using tables tutorial in the help documentation Help > Tutorials > Tutorial 3. This includes an example of dynamic lists in a table where the list values shown vary according to other columns in the row.
0 x
-
- Ebase User
- Posts: 86
- Joined: Thu Mar 08, 2018 2:52 pm
Re: Dynamic list field mapped to table column not working
Thank you. That all works for a plain form. I have a three level categorisation.
But when I use a repeater, each row from a table having these three category fields per row, only the first field is retrieved when using a dropdown. The related next two fields are not. Not even the integer (key) value is shown. Only when I change the two fields from dropdown to text, they show the integer value of the field.
I added an extra (required) field like you suggested in the dynamic list, but no success.
But when I use a repeater, each row from a table having these three category fields per row, only the first field is retrieved when using a dropdown. The related next two fields are not. Not even the integer (key) value is shown. Only when I change the two fields from dropdown to text, they show the integer value of the field.
I added an extra (required) field like you suggested in the dynamic list, but no success.
0 x
-
- Moderator
- Posts: 1342
- Joined: Wed Sep 12, 2007 12:49 pm
Re: Dynamic list field mapped to table column not working
If you see nothing against a list, it means the system has been unable to build the list for some reason. Do you see any messages on the execution log - something like "Unable to build list....".
0 x
-
- Ebase User
- Posts: 86
- Joined: Thu Mar 08, 2018 2:52 pm
Re: Dynamic list field mapped to table column not working
It works now. Made a mistake. The mapping of the row fields were set to other non table fields. After changing to table fields, it worked.
Thanks for your support.
Thanks for your support.
0 x
Who is online
Users browsing this forum: No registered users and 3 guests