Relationship between tables, incorrect results

[Antonino_Accard]

I have 4 tables related one to many, the relationship between table 1 and table 2 and 3 gives correct results, the relationship between table 3 and 4 gives wrong results.

table1

company_id	User_ID	id
company1	1	1
company2	2	2
company1	5	3

Key = id

Table2

<

company_id	app_id	id	app_key
company1	1	1	company1|1
company1	2	2	company1|2
company1	3	3	company1|3
company2	2	4	company2|2

Key = app_key

Ref company_id (table1)-- SELEct (table2 [app_key], ([company_id] = [_THISROW-1].[ company_id]))

ok

Table 3

</

company_id	com_id	id	app_key	_ComputedKey
company1	com1	1	company1|1	company1|com1
company1	com2	2	company1|2	company1|com2
company1	com4	3	company1|3	company1|com4
company2	com6	4	company2|2	company2|com6

Key= _ComputedKey

REF app_key (table2)-- select (table3 [_ComputedKey], ([app_key] =[_THISROW-1].[app_key])) ok

Table4

</

activity_id	date	key_activity	Description	other fields
1223556	25/02/2020	company1|com1	Description com1 app 1	other fields
1223559	25/02/2020	company1|com1	Description com1 app 1	other fields
1223472	26/02/2020	company2|com6	Description com6 app 2	other fields

Key=activity_id

Ref key_activity (table 2) select (Table4 [activity_id], ([_THISROW].[key_activity] =[_THISROW].[_ComputedKey]),true)

Result

</

activity_id	date	key_activity	Description	other fields
1223556	25/02/2020	company1|com1	Description com1 app 1	other fields
1223559	25/02/2020	company1|com1	Description com1 app 1	other fields

for all references of table 3

the automatic setting gives the same result.

Solution?

[WillowMobileSys]

Antonino_Accardo:

for all references of table 3

the automatic setting gives the same result.

Ref key_activity (table 2) select (Table4 [activity_id], ([_THISROW].[key_activity] =[_THISROW].[_ComputedKey]),true)

A little confused where the problem is for the table 3 references. In the line above, did you actually mean “table 3” instead of “table 2”.

If so, the “Result” you show appears to be correct based on the expression and table data provided. What exactly is the problem?

[Antonino_Accard]

WillowMobileSystems:

table3"

Yes.

By selecting from table 3 ( _ComputedKey ) “company2 | com6”
the result is

activity_id	date	key_activity	Description	other fields
1223556	25/02/2020	company1|com1	Description com1 app 1	other fields
1223559	25/02/2020	company1|com1	Description com1 app 1	other fields

[WillowMobileSys]

select (Table4 [activity_id], ([_THISROW].[key_activity] =[_THISROW].[_ComputedKey]),true)

The result seems correct.

In the expression above, [_THISROW] refers to the table 3 row and assuming that row is where [_ComputedKey] = “company1|com1” AND considering you have the Unique results flag set - which means that you want unique [activity_id] from Table 4 - you will get as a result the two rows highlighted in your Result table.

What do you expect as a result?

[Antonino_Accard]

WillowMobileSystems:

What do you expect as a result?

[_ComputedKey] = “company2|com6”

the correct result is activity_id =1223472 not 1223556 and not 1223559.

The problem is between table 3 and 4, but I don’t understand the error.

I corrected [_THISROW] -1, but it doesn’t fix the problem.

[WillowMobileSys]

Antonino_Accardo:

[_ComputedKey] = “company2|com6”

the correct result is activity_id =1223472 not 1223556 and not 1223559.

Based on the result, you are not getting the Table 3 row key you are expecting

To help more, we need to see the actual usage of the SELECT() expression within your app so we can hone in on exactly where the issue is. Can you provide images showing where and how you are using the expression?

[Antonino_Accard]

Table3 = comandi_attivi

result

table4= attività

[Antonino_Accard]

correcting the expression of the select = select (activity [activity_id], ([attivià_key] = [_ THISROW]. [_ ComputedKey]), true)
always error.

[WillowMobileSys]

Ah, I didn’t notice this in the post - even though I copied the text!!!

In the screenshot below, The first [_THISROW] needs to be removed. You cannot have a selection criteria with `[_THISROW].[Column A] = [_THISROW].[Column B]. Basically, by doing this, the expression is comparing the operating row to itself instead of to the selection Table.

[Antonino_Accard]

I corrected it, but the result doesn’t change

[WillowMobileSys]

Antonino_Accardo:

[attivià_key]

Make sure you are using the column [attivià_key] and not [app_key]. I saw some difference with these two.

[WillowMobileSys]

Antonino_Accardo:

Ref company_id (table1)-- SELEct (table2 [app_key], ([company_id] = [_THISROW-1].[ company_id]))

By the way, as I look through this a bit closer trying to understand where the confusion might be, the usage of [_THISROW-1] is odd.

[_THISROW-1] refers to the “grandparent” table. In the expression above it seems you are attempting to select rows from Table 2 on the current Table 1 row.

But by using [_THISROW-1] the expression will try to access the grandparent table of table 2. If Table 1 is the highest table, has no parent, then TAble 2 would have no “grandparent” table.

I’m not 100% clear on what the [_THISROW-1] value will return in this case, but I would expect it is an empty string and in turn the SELECT() expression would return no rows.

[Antonino_Accard]

I checked it

[WillowMobileSys]

Ok, first to double check, in table “attivita” the column [id_activita] is set as the key column?

If so, the expression for the column [Related attivitas] in table “comandi_attivi” should be:

select(attivita[activity_id], [attivita_key] = [_THISROW].[_ComputedKey]), true)

If that is correct, then make sure you have saved and then run a test again.

If you are relying on the test results, make sure you are looking at the correct details.

[Antonino_Accard]

Antonino_Accardo:

select (activity [activity_id], ([attivià_key] = [_ THISROW]. [_ ComputedKey]), true)

attivià_key it’s correct

[Antonino_Accard]

You want to log in to check?

[WillowMobileSys]

Sure! I don’t mind checking it out for you. Give me access and I’ll take a look.