Is an "OR" relationship possible?
In the relationship editor, I can make multiple relationshhip critieria based on "AND". Is it possible to make "OR" relationships?
"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
DEF ABC DEF ABC
The Accounts Record for Account ABC will display both those transactions in the portal.
I think a portal filter will do this, but i have to upgrade to fmp 11 or 12
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?
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.
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
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:
perform the equality compare?
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.
Accounts TableAccount Number = ABC
Transaction TableTransaction record 1, SourceAccount = ABCTransaction 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.
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:
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.
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.
Retrieving data ...