5 Replies Latest reply on Aug 8, 2010 6:20 PM by cameraeyes

Creating a Relationship between two Tables that shows the records that don't match in a Portal

Title

Creating a Relationship between two Tables that shows the records that don't match in a Portal

Post

I am struggling to create a relationship between two tables so that I  can display in a portal a list of records in one database that are not related to the records in a portal.

For example, if Table A has a number field that has 10 values from 1 to 10 and Table B has a number field that has 10 values from 5 to 15.  I would like a portal to display values 1-4 from Table A.

I have upgraded to FM 11.0 and am able to use filtering to achieve this but I would like to incorporate this in a database that runs on FM 10.0.  Any suggestions on how this can be done?

David

• 1. Re: Creating a Relationship between two Tables that shows the records that don't match in a Portal

In table B, define a calculation field that returns the constant 5 as a number.

Link it to a table occurrence of table A with an inequality operator:

Table B::FiveField > TableALessThan5::NumberField

Create a portal to TableALessThan5 and you'll see the records you want.

• 2. Re: Creating a Relationship between two Tables that shows the records that don't match in a Portal

Instead of limiting to a constant of 5, since it was MY perception that you wanted ALL records not related and you only provided the 1-4 as an example, I would do this:

Create another occurrence of Table B (and call it Table_B_All).  Join on 'x' which is cartesian join.  Create a calculation in Table B (result is number) as:  List ( Table_B_All ; numberField ).  Then create a join from this new calculation to your Table A but use the <> symbol (≠ ) between them.  Place a portal based upon Table A on your layout.

In this way, if Table A gets records 16 and 17, they will appear in the 'not related to Table B' portal as well.

• 3. Re: Creating a Relationship between two Tables that shows the records that don't match in a Portal

LaRetta,

Thank you so much for your response.  You are correct, I want to see all records not related.  Unfortunately, I am having a difficult time following your steps.  Does it matter which fields create the cartesian join?  Also, what fields am I linking in the second join?  Can you please elaborate on this?

Thanks!

• 4. Re: Creating a Relationship between two Tables that shows the records that don't match in a Portal

I'm happy to explain in more detail:

Create another occurrence of Table B (and call it Table_B_All).

Select your Table B and then click the ++ arrow to create another occurrence of the table.  It isn't another table, rather just another representation of it in the graph.

Join on 'x' which is cartesian join.

When joining by cartesian product, you can join on any two fields (except for summary and container) and it doesn't matter whether they are indexable on the child side nor whether the field has a value at all so you can join on two globals if you wish.  In fact, you can then delete out the fields and the cartesian relationship will still work.  Cartesian is like a constant that some people still create (but hasn't been needed since vs. 7) because it relates all records in one TO to all records in another TO.  When you relate your two table occurrences, select the last relational which is the 'X' which represents Cartesian join.

Create a calculation in Table B (result is number) as:  List ( Table_B_All ; numberField ).

This calculation will produce a multi-line of all of the numbers in Table B.  Table_B_All represents the new table occurrence of Table B that you created and 'numberField' represents your number field.  If unfamiliar with creating calculations, create your calculation and the calculation box will pop open.  Ignore the uppermost which says 'evaluate this calc from context.

Type List ( ... and from the next pop-up, select Table_B_All and then double-click numberFieldEnd with )

Note: I had a typo in my prior post (my apology) and it should end up looking like this:  List ( Table_B_All::numberField ).  Name this calculation c_all_numbers.

Then create a join from this new calculation (c_all_numbers) to your Table A but use the <> symbol (≠ ) between them.  Place a portal based upon Table A on your layout.

What you end up with is a multiline of all numbers in Table A in that single multi-line calculation field.  When you relate it now to Table B using 'not equal', only numbers in Table B which are NOT in Table A will relate.

• 5. Re: Creating a Relationship between two Tables that shows the records that don't match in a Portal

Thank you so much for taking the time to explain it in more detail!!!!! Works like a charm.