Relationships other than AND

[Ravn]

Hi People,

 

I just recently discovered the great ease and potential of FileMaker, but has now started wondering about some things. First I wonder how to make relationships that are not AND but any other boolean operator. What I mean is, if I make a relationship by imposing more than one restriction (=, , !=, ...) they are always connected with AND, but I would really like to have OR, XOR and so on.. It this possible to do?

 

Regards,

Robert

[AHunter3]

Not literally no: relationships involving more than one pair of matches are always AND

 

Thisfield ≥ Thatfield AND

Alsofield = Otherfield

 

etc; you can't use OR instead, not like you can use AND.

 

But you can usually construct the "or" logic by using a multikey as one of your fields:

 

Consider CalcField X to be a calculation field of result type "text" defined as Field A & "¶" & Field B

 

Whereas no, you cannot do this:

 

Field A = MatchField OR

Field B = MatchField

 

...you can nevertheless do this:

 

CalcField X = Matchfield

 

 

 

...which serves the same purpose.

[Ravn]

Ah, thanx!

 

However I don't quite understand the idea of this implementation. What if I wanted XOR? or any other boolean operator?

[AHunter3]

You say you don't understand the idea of this implementation?

 

If CalcField X = Field A & "¶" & Field B, FileMaker is going to treat that field as containing each of those values; any table related TO or FROM this table via that field Calcfield X will match up on the basis of the same value as Field A or the same value as Field B.

 

This is called a multikey because each value in the field works as a separate key in a relationship.

 

 

CalcField X.......... Matchfield

 

"Black................."Black"

Red"

 

is a match

 

 

"Black................."Red"

Red"

 

 

is also a match.

 

XOR can be a bit more complicated. If you really need XOR, I can post a sample db but you'd have to join up to download it (I see you're a guest member).

 

You can usually work your way around "AND NOT" by figuring out some way to set up fields that will let you do AND instead.

 

It's not true boolean equivalency, don't get me wrong. FileMaker simply does not offer us boolean capabilities at this level (yet). You cannot define a relationship as

 

Table A..................Table B

 

Field A ≥ Field B AND

(Field C = Field D OR

..( Field E ≠ Field D AND Field E ≥ Field F)) AND NOT

Field G ≠ Field H

[poetnabotl]

 

Whereas no, you cannot do this:

 

Field A = MatchField OR

Field B = MatchField

 

...you can nevertheless do this:

 

CalcField X = Matchfield

 

 

 

...which serves the same purpose.

 

What about:

 

Field A = MatchField OR

Field B = OtherMatchField

 

?

[AHunter3]

Nope, can't literally do that either, but again you could do a workaround.

 

NewCalcField = MatchField & "¶" & "|"&OtherMatchField

 

CalcField X = Field A & "¶" & "|" & Field B

 

 

relationship:

 

CalcField X = NewCalcField

 

 

again that's not an XOR, just an OR. It will match either.

 

 

edit: edited for consistency in name given to field in example

[poetnabotl]

Makes perfect sense. Would never have occurred to me. Wish I had a higher IQ. smiley-laughing

[Leon4004]

Thanx a lot people. I've been working on the same problem. Although I didn't post a comment yours realy saved my day! Cheers!

[imacuser]

I've used this method to show related data in a portal, however, it doesn't seem to work if the match field is a calculation field.

 

Any wisdom on this is appreciated.

 

FMP 8.5 win

[Ender]

Calc fields do work as match keys, but they must be stored and indexed. That means you can't refer to related fields, aggregate functions, or get() functions.

 

There's usually a way around this problem by moving the conditions to the parent side, but we would need specifics about your particular situation in order to advise.

[AHunter3]

Calc fields do work as match keys, but they must be stored and indexed. That means you can't refer to related fields, aggregate functions, or get() functions.

 

 

Also: if the goal is to create new portal records via the relationship, no component of the portal side of the relationship can be a calc field. View yes, create no.

 

FileMaker is NOT going to conjure up a new record where Field A = "hello" and Field B = "world" to provide a new portal record where CalcField C = "hello world". With or without other fields also being involved in the relationship definition.

[brsamuel]

Thank you very much. I too have been trying to do this, or find a way around. However, I need to accomplish this with dates. (does the startdate OR the enddate fall within the fiscal year?)

 

I attempted to accomplish the same thing by using a calc field that reflected the necessary logic (if my conditions are met it = 1, otherwise 0) but that wasn't working for me either; stlll not sure why that failed!

 

So, any way of making this work with dates? I really don't want to create a multikey field with 365.25 dates in it.

 

 

Thanks

Samuel

[Ender]

Welcome Samuel,

 

Strangely enough, in this case, a multi-key is not the best way to go.

 

For a relationship based on date or time ranges, use a multi-criteria range relatonship. If your data has a Start Date and End Date, and there's a fiscal year Start Date and End Date, then you'd have a range-to-range relationship, looking something like this:

 

Interface Data =

Interface::FiscalStartDate ≤ Data::End Date

AND Interface::FiscalEndDate ≥ Data::Start Date

 

That will match records that overlap the fiscal year at all.

 

 

If you only want to see records that are entirely within the fiscal year, it's a little different:

 

Interface Data =

Interface::FiscalStartDate ≤ Data::Start Date

AND Interface::FiscalEndDate ≥ Data::End Date

 

 

BTW: There is a clever multi-key technique for range relationships, called Smart Ranges. You can read about it here if you want to blow your mind:

http://www.onegasoft.com/tools/smartranges/

 

This technique was necessary prior to FileMaker 7.

[brsamuel]

For a relationship based on date or time ranges, use a multi-criteria range relatonship. If your data has a Start Date and End Date, and there's a fiscal year Start Date and End Date, then you'd have a range-to-range relationship, looking something like this:

 

Interface Data =

Interface::FiscalStartDate ≤ Data::End Date

AND Interface::FiscalEndDate ≥ Data::Start Date

 

 

Thanks! That's it exactly. I must have had 'OR' stuck in my brain.

 

sigh... I'm going to go change may skill level back to novice, and perhaps hit my head against the wall a little less vigorously.