Relationship between tables, incorrect results

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?

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?

1 Like

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

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.

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

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?

Table3 = comandi_attivi

result

table4= attività

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

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.

I corrected it, but the result doesn’t change

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

I checked it

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.

attivià_key it’s correct

You want to log in to check?

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