list1 - list2 - list3 (issue with filter for list3)

[JPMPA]

I have 3 lists parent - child - grandchild.
Each returns primary keys - these are assigned to the arguments: argument_key1, argument_key2 and argument_key3
The key fields are say INVOICE_ID, INVOICE_LINE and INVOICE_DETAIL as follows
parent - key INVOICE_ID
child - key INVOICE_ID, INVOICE_LINE
grandchild - INVOICE_ID, INVOICE_LINE and INVOICE_DETAIL
When I specify that the parent of list2 is list1 - the filter works great and details are shown when I move from list1 to list2 panel.
Filter: INVOICE_ID = :argument_key1
So for so good.

BUT for list2 and list3
The filter is really creating a problem, and I am required to provide the key as I have specified the parent of list3 as list2
(wish to go by book and specify this relation as specified above- to avoid work-arounds)
When I assign the filter as INVOICE_ID + '|||' + INVOICE_LINE = :argument_key1
for some unknown to me reason - it is converted to ((INVOICE_ID + '|||') + INVOICE_LINE) = :argument_key2

Please help.

[Davidk]

Re: list1 - list2 - list3 (issue with filter for list3)

Put together a sample UX component with Lists and static data and we can have a look.

[JPMPA]

Re: list1 - list2 - list3 (issue with filter for list3)

Thanks - please see the attached UX! (NOTE: I replaced the old UX about 5 minutes ago - as the old one was not the latest (did not have list3 showing list2 as parent))

As mentioned - On list2 the filter I used is
INVOICE_ID = :my_key_from_list1
The above works great.

But the following has problems.
On list3 the filter I used is (since the key field are seperated by '|||' for Primary key returned by list)
INVOICE_ID + '|||' + LINE_NBR = :my_key_from_list2
It is automatically changing this to
((INVOICE_ID + '|||') + LINE_NBR) = :my_key_from_list2

I wish all three of this lists to return the primary key because, I am using the keys else where (in other words do not wish to use the work around to replace the "return value" of list2 to field:LINE_NBR instead of existing PrimaryKey) Note: INVOICE_ID is char, LINE_NBR is a number (is the number (int) messing it up - but the field LINE_NBR is a number and used by other applications).

TEST_LIST1_UX1.a5wcmp

create_tables_for_list1_list2_list3.txt

data_for_tables_for_list1_list2_list3.txt

[Davidk]

Re: list1 - list2 - list3 (issue with filter for list3)

The trick to this stuff is to stay inside the SQL Genie until you get a valid result... using any of the SQL means to get that result. Using this SQL Statement seems to get the results you need...

SELECT INVOICE_ID, LINE_NBR, LINE_DETAIL, AMOUNT3, DESCRIPTION3 
FROM INVOICE_DETAIL 
WHERE (( Convert( nvarchar(50) , INVOICE_ID)  + '|||') +  Convert( nvarchar(50) , LINE_NBR) ) = :my_key_from_list2

[JPMPA]

Re: list1 - list2 - list3 (issue with filter for list3)

Thanks David. This was exactly what I was looking for!

[JPMPA]

Re: list1 - list2 - list3 (issue with filter for list3)

I changed the logic little bit so that it will use the index correctly. [field1|||field2|||field3 apparently did not use the index even though had index for these primary fields - was looking for individual values as below]. This is for SQL Server - basically extracts the field1 and field2, etc from the concatenated value separated by '|||' using SUBSTRING, CHARINDEX etc.

With 2 field primary keys - filter

INVOICE_ID = SUBSTRING(:my_key_from_list1, 1, ( CHARINDEX('|||', :my_key_from_list1) - 1)) AND LINE_NBR = SUBSTRING(:my_key_from_list1, ( CHARINDEX('|||', :my_key_from_list1) + 3), 100)

With 3 field primary keys - filter

INVOICE_ID = SUBSTRING(:my_key_from_list2, 1, ( CHARINDEX('|||', :my_key_from_list2) - 1)) AND LINE_NBR = SUBSTRING( SUBSTRING(:my_key_from_list2, ( CHARINDEX('|||', :my_key_from_list2) + 3), 100) , 1, ( CHARINDEX('|||', SUBSTRING(:my_key_from_list2, ( CHARINDEX('|||', :my_key_from_list2) + 3), 100) ) - 1)) AND DETAIL_LINE_NBR = SUBSTRING( SUBSTRING(:my_key_from_list2, ( CHARINDEX('|||', :my_key_from_list2) + 3), 100) , ( CHARINDEX('|||', SUBSTRING(:my_key_from_list2, ( CHARINDEX('|||', :my_key_from_list2) + 3), 100) ) + 3), 100)

Thanks.

[JPMPA]

Re: list1 - list2 - list3 (issue with filter for list3)

Just to set the records straight. The above theoretically looks correct. The response was actually bad for small data when tested. May be for very large data it will work better compared to previous filter clause. Thanks!