10 Replies Latest reply on Apr 29, 2012 12:20 PM by Sorbsbuster

    Is an "OR" relationship possible?

    ronm

      Title

      Is an "OR" relationship possible?

      Post

      In the relationship editor, I can make multiple relationshhip critieria based on "AND". Is it possible to make "OR" relationships?

        • 1. Re: Is an "OR" relationship possible?
          ronm

          I think a portal filter will do this, but i have to upgrade to fmp 11 or 12

          • 2. Re: Is an "OR" relationship possible?
            Sorbsbuster

            A portal filter would be an easy way, but you can still do it.  BTW: what version of FM are you using?

            The principle of the relationship is to have the related values as return-separated list.  Which way do you want the OR to work?

            • 3. Re: Is an "OR" relationship possible?
              ronm

              i'm using fmp 10. i have a table called "transactions". there is a field for the source account of the transaction and one for the destination account. for a given account, i want to retrieve all the transactions that have that account as a source OR a destination. i can get what I want as 2 separate instances of transaction, and this will work, but i'm finding it difficult to produce reports showing transaction in and out sorted by date.

              • 4. Re: Is an "OR" relationship possible?
                Sorbsbuster

                In the Transaction Table create another calculated field.  Make it List ( SourceAccount ; DestinationAccount ).  Then make a relationship from the Accounts Table to the Transaction Table:

                 

                Accounts::AccountNumber = Transactions::ThatNewField

                • 5. Re: Is an "OR" relationship possible?
                  ronm

                  humm, List() creates a return separted list containg the account numbers, a list length 2 and puts it into and puts it into ThatNewField. is that correct? how will:

                  Accounts::AccountNumber = Transactions::ThatNewField

                  perform the equality compare?

                  • 6. Re: Is an "OR" relationship possible?
                    Sorbsbuster

                    Sorry, I don't understand your question.  Have you tried it?

                    If you have a table with two fields containing the data A in one field and the data B in another field, and a 3rd field that concatonates them to be:

                    A & <return character> & B

                    and then use that concatonated field as the right side of a relationship, then if the field on the left side of the relationship contains the data A or B it will match that record.

                    You have:

                    Accounts Table
                    Account Number = ABC

                    Transaction Table
                    Transaction record 1, SourceAccount = ABC
                    Transaction record 2, DestinationAccount = ABC

                    The Relationship as I described will list both those transactions in a portal on the Accounts Table on the record for Account Number ABC.

                    • 7. Re: Is an "OR" relationship possible?
                      ronm

                      i haven't tried it yet, i will in the morning. i think that will be the only way i'll understand it. here's what i see in your example:

                      Accounts Table
                      Account Number = ABC

                      Transaction Table
                      Transaction record 1, SourceAccount = ABC
                      Transaction record 2, DestinationAccount = ABC

                      if i concatenate Transaction record 1&2 i should get ABCABC. will ABC = ABCABC ? i don't know fmp well, relationship comparisions do suprise me sometimes.

                      • 8. Re: Is an "OR" relationship possible?
                        Sorbsbuster

                        "if i concatenate Transaction record 1&2 i should get ABCABC"

                        No - I said: A & <return character> & B ( or List ( Source ; Destination ) ).  The return character will be in quotations, and can be picked up as the pilcrow character ("¶") from the calculation dialogue

                        And it will not concatonate Record 1 and Record 2, it will concatonate the two fields from the same record.  If I assume that each transaction has data in both fields then Transaction Records 1 and 2 may look like:

                        Source       Destination           ConcatonatedField

                        ABC           XYZ                        ABC
                                                                      XYZ

                        and

                        DEF           ABC                        DEF
                                                                     ABC

                        The Accounts Record for Account ABC will display both those transactions in the portal.

                        • 9. Re: Is an "OR" relationship possible?
                          ronm

                          Thanks,

                          I had heard about "multi-keys", but didn't understand them. i've been fooling around with for a bit now. very handy relationship tool. 

                          thanks very much for your help and patience.

                          ron

                          • 10. Re: Is an "OR" relationship possible?
                            Sorbsbuster

                            No problem.