Filtering and selecting between four tables

[Pointer]

I have four tables, as I mentioned, there are references between them. How can I show the Table 4 data in Table 1 by filtering it according to Table 3? So how can I get report from table 1?

Table 1(Reports)[GID]

Table 2(Equipment)[TID]

Table 3 (Records)[ID].[TID].[GID]

Table 4(Analysis)[ID].[TID]

[Pointer]

ORDERBY(FILTER(“Table 4”, ([_ROWNUMBER] = MIN(SELECT(Table 4[_ROWNUMBER], AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table 3[TID], [_THISROW].[GID] = [GID]))))))), [TID],false)

I solved it this way. Thanks @Steve for this method.

[Pointer]

@Steve Hi,

ORDERBY(FILTER(“Table 4”, IN([TID], SELECT(Table1[TID], AND([_THISROW-1].[TID] = [TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true)

I do the outer grouping with the above formula. No problem, but there is a problem with the inner grouping, the data are mixed. How should the formula for inner grouping be?

[Steve]

Pointer:

How should the formula for inner grouping be?

The relationships between your tables are not clear. The expression here doesn’t match the previous description of the tables.

[Pointer]

Pointer:

Table 1(Reports)[GID]

Table 2(Equipment)[TID]

Table 3 (Records)[ID].[TID].[GID]

Table 4(Analysis)[ID].[TID]

reference columns for the second and third columns

<<Start: ORDERBY(FILTER(“Table 4”, AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true)>><<[Analysis Date]>>

It is divided into groups in this way, but the same date data comes only

[Pointer]

Can you help me please? I just couldn’t.

[Pointer]

Is there any way to convert rows to multiple columns in reports.?

Row 1 …Date1…Col.
Row 2…Date2…Col.
Row 3…Date 3…Col.

Row1 Row2 Row3
Date1 Date 2 Date3
Col. Col. Col.
… … …
… … …
… … …

[Pointer]

Row 1
Date 1
Col.
…
…

Row 2
Date 2
Col.
…
…

Row 3
Date 3
Col.
…
…
I can transform as a single column.

[Pointer]

Data in column instead of row Questions

With the proposed method, I tried. Below is the result. This is not the format what I want. [image] I want format as shown in below picture. Is there a way to do it? Thank you. [image]

Is there any improvement in this matter?

[Pointer · Accepted solution]

I fixed the problem, the problem is left as Table 4 column Id column number. I did “UNIQUEID ()” and there was no problem. The above formula works correctly. It was a very good experience for me.

[Pointer]

I want to get value according to the last analysis date why this formula doesn’t work

<<Start: INDEX(ORDERBY(FILTER(“Table 4”, AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true),1)>><<[Analysis Date]>>

[Steve]

Use TOP() instead of INDEX().

See also:

help.appsheet.com

INDEX()

One specific list item

help.appsheet.com

TOP()

Initial list items

[Pointer]

<<If: [Analysis Date]=INDEX(ORDERBY(FILTER(“Table 4”,  AND([_THISROW-1].[TID] = [TID], IN([TID], SELECT(Table3[TID], [_THISROW].[GID] = [GID])))), [Analysis Date],true),1)
>><<[Analysis Date]>><<EndIf>>

This did not happen, what should I do, to show the date of the last analysis. can you help me?