cancel
Showing results for 
Search instead for 
Did you mean: 

How do I conditionally follow a link????

Former Member
0 Kudos

Post Author: tazer98

CA Forum: Data Connectivity and SQL

Hello All,

Back again. Its getting a little tougher as I get more involved in CR. Hereu2019s my latest situation.

I have 4 tables (A, B, C and D). There are links between tables: A-B, A-C and C-D. Keys are: KeyAB, KeyAC and KeyCD. Link A-B is a one-to-one, A-C is a one-to-one and table C-D is a one-to-many.

After retrieving a row from table A I make a determination in getting my data (on-hand quantity) from table B or table D.

My pseudo code would be:

Retrieve table A row

If tableA code = 1 then

Retrieve tableB row

Print tableBQty

Else

Retrieve tableC row

Retrieve all related tableD rows

Print tableDQty

End

Go to retrieve another from tableA

Well I really donu2019t know how to put this type of logic into CR. I believe it lies in the link options of the database.

Can someone help me out on this one?

Thanks,

Hexman

Accepted Solutions (0)

Answers (4)

Answers (4)

Former Member
0 Kudos

Post Author: tazer98

CA Forum: Data Connectivity and SQL

Very thourough answer. I'll be trying this today.

Thanks,

Hexman

Former Member
0 Kudos

Post Author: tazer98

CA Forum: Data Connectivity and SQL

Thanks for all the info. The suppression of sections sounds like it will do the trick. Yes, I do want to show D's detail, row by row.

Hexman.

Former Member
0 Kudos

Post Author: GraemeG

CA Forum: Data Connectivity and SQL

Clarification on file links (and apologies if this is old hat) - when I'm working on multi-file reports with combinations of one-to-one, many-to-one and one-to-many, I tend to change my links to outer joins that aren't enforced - I can then control my inclusion/exclusion and any other record discrepancy error trapping. Inner joins require a matching record to exist in both files that you are linking.

Are you wanting to show D's records in detail or summary?

Former Member
0 Kudos

Post Author: Jagan

CA Forum: Data Connectivity and SQL

You could have two sections, both conditionally suppressed. Put the B.qty value into one section and conditionally suppress if A.code <> 1. Put the fetch of the C-D data into a subreport, place it in its own section and conditionally suppress the section if A.code = 1.. i.e. only fetch the C-D data if you need it, record by record. However, this could slow the report down a bit if you've got a lot of records.

Otherwise you could join all tables as you've described, which'll give you N rows per record in A where N is the number of rows in D for that record (or at least 1 if you need to left join to either C or D). You could group on A's key accordingly (giving you N detail records per group) and print (using a formula to chose the value you want to display) in the A group footer. This would affect any sums / running totals etc. if you have any 'cos of the N rows in D.