Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Select data in a row if exist in others Post Reply Post New Topic
Author Message
techlnuff
Newbie
Newbie
Avatar

Joined: 18 Nov 2018
Location: Italy
Online Status: Offline
Posts: 2
Quote techlnuff Replybullet Topic: Select data in a row if exist in others
    Posted: 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.
IP IP Logged
Valert16
Groupie
Groupie
Avatar

Joined: 21 Mar 2016
Location: Spain
Online Status: Offline
Posts: 57
Quote Valert16 Replybullet Posted: 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.
IP IP Logged
techlnuff
Newbie
Newbie
Avatar

Joined: 18 Nov 2018
Location: Italy
Online Status: Offline
Posts: 2
Quote techlnuff Replybullet Posted: 24 Nov 2018 at 11:35am
Very well, It's what I need! Thank you so much! Thumbs%20Up
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.