Technical Questions: Select data in a row if exist in others

Posted By: techlnuff — 20 Nov 2018 at 12:57pm

Hi!

Please, I need help for a report that contain a group, in that group i need to put a new value with a formula.

The data table that i have is like that:

S NR ID_DOC DATE LINK R

8    1    38    2018-03-31 00:00:00.000    0    0
9    1    39    2018-01-31 00:00:00.000    0    0
9    2    39    2018-02-28 00:00:00.000    0    0
9    3    39    2018-03-31 00:00:00.000    0    0
9    4    39    2018-04-30 00:00:00.000    0    0
10    1    40    2018-01-31 00:00:00.000    0    0
10    2    40    2018-02-28 00:00:00.000    0    0
10    3    40    2018-03-31 00:00:00.000    0    0
11    1    41    2018-01-31 00:00:00.000    0    0
12    1    42    2018-02-28 00:00:00.000    0    0
12    2    42    2018-03-31 00:00:00.000    0    0
12    3    42    2018-04-30 00:00:00.000    0    0
13    1    43    2018-02-28 00:00:00.000    0    0
13    2    43    2018-03-31 00:00:00.000    0    0
13    3    43    2018-04-30 00:00:00.000    0    0
13    4    43    2018-05-31 00:00:00.000    0    0
14    1    44    2018-01-31 00:00:00.000    0    0
14    2    44    2018-02-28 00:00:00.000    0    0
15    1    45    2018-02-28 00:00:00.000    0    0
16    1    46    2018-02-28 00:00:00.000    0    0
16    2    46    2018-03-31 00:00:00.000    0    0
17    1    47    2018-02-28 00:00:00.000    0    0
17    2    47    2018-03-31 00:00:00.000    0    0
18    1    48    2018-02-28 00:00:00.000    0    0
19    1    27    2018-02-28 00:00:00.000    0    0
20    1    28    2018-01-31 00:00:00.000    0    0
21    1    29    2018-02-28 00:00:00.000    0    0
23    1    32    2018-02-28 00:00:00.000    0    0
24    1    34    2018-01-31 00:00:00.000    0    0
26    1    35    2018-02-28 00:00:00.000    0    0
27    1    31    2018-02-28 00:00:00.000    0    0
27    2    31    2018-03-31 00:00:00.000    0    0
28    1    36    2018-02-28 00:00:00.000    0    0
65    1    4    2018-01-01 00:00:00.000    8    1
66    1    4    2018-01-01 00:00:00.000    4    1
67    1    4    2018-01-01 00:00:00.000    12    1
68    1    4    2018-01-01 00:00:00.000    12    2
69    1    4    2018-01-01 00:00:00.000    12    3
70    1    4    2018-01-01 00:00:00.000    9    1
71    1    4    2018-01-01 00:00:00.000    9    2
72    1    4    2018-01-01 00:00:00.000    9    3
73    1    4    2018-01-01 00:00:00.000    9    4
74    1    4    2018-01-01 00:00:00.000    7    1
75    1    4    2018-01-01 00:00:00.000    13    1
76    1    4    2018-01-01 00:00:00.000    13    2
77    1    4    2018-01-01 00:00:00.000    13    3
78    1    4    2018-01-01 00:00:00.000    13    4

I need, for every row, if LINK and R are equal to S and NR, get DATE of the row where are S and NR.

For example, in that table, if I'm at the row with S = 68, with LINK = 12 and R=2 I should have DATE = 2018-03-31.

How can I obtain that?

Every print of the report will print different data so I don't know what row I'll see, I need to check every row I think.

Thanks.

Posted By: Valert16 — 21 Nov 2018 at 1:11am

Assuming that every details row belongs to a single table, you should add this same table again to the selected tables in database expert. CR will propose an alias for this newly added table, wich allows you to treat it as any other table.
Next, in the links window, you must create a link between field "LINK" of the original table to field "S" of the alliased table. Repeat this between fields "R" (original) and "NR" (alliased). Select any of the lines representing the links, and press the "Link Options" button and select "Left Outer Join" as join type. Be sure the outer join goes from the original to the alliased table (see the arrows direction). This allows you to get all the rows even if no matches are found between the two "repeated" tables.

This should work if every row with given values of fields LINK and R finds one or none rows in the alliased table with matching values of fields S and NR respectively.

Finally, create a formula for showing the date you want:

If IsNull({YourTableAllias.DATEFIELD}) Then
{YourTable.DATEFIELD}
Else
{YourTableAllias.DATEFIELD}

Replace table and field names by yours.

I assume you want to show the original date field if LINK and R don't match S and NR.

Hope this helps.

Posted By: techlnuff — 24 Nov 2018 at 11:35am

Very well, It's what I need! Thank you so much! Thumbs%20Up