Dynamic list field mapped to table column not working

Post any questions you have about using the Verj.io Studio, including client and server-side programming with Javascript or FPL, and integration with databases, web services etc.

Moderators: Dave, Jon, Steve, Ian

Segi
Ebase User
Posts: 591
Joined: Mon Dec 09, 2013 6:37 pm

Dynamic list field mapped to table column not working

#1

Postby Segi » Tue May 17, 2016 8:33 pm

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
0 x

Hovik
Moderator
Moderator
Posts: 184
Joined: Tue Sep 11, 2007 8:58 am

#2

Postby Hovik » Thu May 19, 2016 9:39 am

Segi,

Sounds like a mapping issue. Couple of suggestions:

Make sure "When list is built' for the table column with the list is set to Each Display.
Go back to using the original dynamic list and check the the Debug checkbox. At runtime you will see the actual query being run for EACH row.
0 x

Segi
Ebase User
Posts: 591
Joined: Mon Dec 09, 2013 6:37 pm

#3

Postby Segi » Wed May 25, 2016 7:03 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.
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

#4

Postby Jon » Thu May 26, 2016 10:03 am

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.
0 x

Segi
Ebase User
Posts: 591
Joined: Mon Dec 09, 2013 6:37 pm

#5

Postby Segi » Thu May 26, 2016 3:31 pm

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.
0 x

User avatar
dvanhussel
Ebase User
Posts: 161
Joined: Fri Oct 19, 2007 12:45 pm
Location: Haarlem, the Netherlands

#6

Postby dvanhussel » Fri May 27, 2016 3:23 pm

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 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.

I have 'fixed' this by calling the function that creates the customList for every repeater row.
0 x

Segi
Ebase User
Posts: 591
Joined: Mon Dec 09, 2013 6:37 pm

#7

Postby Segi » Fri May 27, 2016 5:19 pm

I have done the same thing. I wrote a function to loop through each table row and build the dynamic list for each row and it does work correctly this way.
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

#8

Postby Jon » Tue May 31, 2016 8:42 am

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

Segi
Ebase User
Posts: 591
Joined: Mon Dec 09, 2013 6:37 pm

#9

Postby Segi » Tue May 31, 2016 9:59 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.
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

#10

Postby Jon » Thu Jun 02, 2016 8:08 am

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

User avatar
dvanhussel
Ebase User
Posts: 161
Joined: Fri Oct 19, 2007 12:45 pm
Location: Haarlem, the Netherlands

#11

Postby dvanhussel » Thu Jun 02, 2016 8:38 am

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
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

#12

Postby Jon » Thu Jun 02, 2016 9:46 am

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:

Code: Select all

var itemTypeList = items.itemtype.createCustomList();
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
0 x

User avatar
dvanhussel
Ebase User
Posts: 161
Joined: Fri Oct 19, 2007 12:45 pm
Location: Haarlem, the Netherlands

#13

Postby dvanhussel » Thu Jun 02, 2016 10:07 am

Hi Jon,

Of course, so simple! :oops:

Thanks,

David
0 x

fronsky
Ebase User
Posts: 71
Joined: Thu Mar 08, 2018 2:52 pm

Re:

#14

Postby fronsky » Thu Jul 25, 2019 8:06 am

Jon wrote:
Thu Jun 02, 2016 8:08 am
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
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?
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

Re: Dynamic list field mapped to table column not working

#15

Postby Jon » Fri Jul 26, 2019 9:45 am

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

fronsky
Ebase User
Posts: 71
Joined: Thu Mar 08, 2018 2:52 pm

Re: Dynamic list field mapped to table column not working

#16

Postby fronsky » Fri Jul 26, 2019 1:54 pm

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.
0 x

Jon
Moderator
Moderator
Posts: 1295
Joined: Wed Sep 12, 2007 12:49 pm

Re: Dynamic list field mapped to table column not working

#17

Postby Jon » Fri Jul 26, 2019 2:07 pm

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

fronsky
Ebase User
Posts: 71
Joined: Thu Mar 08, 2018 2:52 pm

Re: Dynamic list field mapped to table column not working

#18

Postby fronsky » Sat Jul 27, 2019 3:02 pm

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.
0 x


Who is online

Users browsing this forum: No registered users and 7 guests