Select data in a row if exist in others
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22702
Printed Date: 02 May 2024 at 8:18pm
Topic: Select data in a row if exist in others
Posted By: techlnuff
Subject: Select data in a row if exist in others
Date 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.
|
Replies:
Posted By: Valert16
Date 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. |
Posted By: techlnuff
Date Posted: 24 Nov 2018 at 11:35am
Very well, It's what I need! Thank you so much!
|