I am trying to link 2 tables (in a sub report) using a formula as there are no fields between them that match. My Formula is just simple if statement that converts transaction codes to match bill codes in the table I need to join.
Is this possible? I have read a few posts that mention using the add command to link the table but I am not an SQL expert, and having some difficulty. So say my formula on the report is called “Relation”
and the table I want to join to is “Table2”, the field I want to link “Relation” with is “billcode” what would the SQL code look like in the add command function?
Thanks in advance for any help anyone can provide.
Not sure if I understand you correctly, but if Table 1 is in the main report and you are trying to link to Table 2 in the subreport then simply create the Relation formula in the main report and link to billcode in the subreport based on @Relation using standard subreport links.
If you are trying to join the 2 tables in the subreport itself, then it is a bit more challenging. Since your Relation formula is an IF statement it may depend a little on what database you are using as to what SQL constructs you have available, but you should be able to create a command, but a database view or bridge table (as suggested by MJRBIM) would be preferable if you have access to the database (or a DBA). If you don’t have access to anyone with SQL expertise I would look up the CASE statement for SQLServer or Decode for Oracle.
(BOB member since 2007-03-23)
(BOB member since 2010-06-18)