Is there a way to add an OR instead of a AND

[Enuma]

In the conditions in my relationships i need to have a logical OR beteew then instead the default AND that always shows UP.

My situation is this I have records with two fields with two diferent dates and i need to make then apear in a portal when the condition based in the dates are true.

So i set two dates a MAX and a MIN then only the records bettew it shows up. It works fine when i want it show records that macth one condition but

with two and if i set equal max and mins one date or the other might be false making all my realation false since each condition is linked by and AND.

So is there anyway to change the AND to an OR or do somthing equivalent.

[Søren Dyhr]

No it's always AND'ed, in order to OR things do you need to gather the related recordID's for each criteria, and merge (add to the end of the other) them into one long list of ID's pilcrow delimited (¶) ... and then use the collection as a new key.

But sometimes isn't it necessary, since you occasionally but not usually can DeMorgan the expression via the ≠ (uneven) operator.

--sd

Edited July 21, 2008 by Guest

[LaRetta]

Actually, I see no reason why you can't use a Case() statement in the child table to create an OR condition which, when it produces true, places the date into the calculation. Then base the relationship on THAT date. For instance:

Case (

Type = "Prepay" ; DateField ;

Category = "Cash" ; DateField2

)

(or even)

Case (

Type = "Prepay" or Category = "Cash" ; DateField

)

... the calculation result should be result of date and let it be indexed.

Edited July 21, 2008 by Guest

[Fenton]

It may be possible to solve this. But we're going to need a more clear explanation, with short complete sentences, and an example or two. It is difficult to understand what you need.

So far I get that you have 2 date fields in the target table. And you want to match a condition in the originating table. But you never really said what that condition was; a single date? a range of dates?

It is possible that you could create a calculation field in the target table, with both dates in it, separated by a return. However, the result must be text (because of the return), which is dodgy, due to the way people write dates, so I'd recommend coercing them to numbers. The result must be indexable.

GetAsNumber (Date1) & ¶ & GetAsNumber (Date2)

Which for today and yesterday would look like:

733244

733243

Targeting that from a single date in another table would work. But only because FileMaker would coerce (actually stores I believe) a single date as a number. But it would not work with "<" or ">", as the target is a return-delimited text field, not a number or date field.

It would work if the originating field was also the same, a bunch of return-delimited numbers (derived from dates) in a text field. There would be various ways to create this. Since you have FileMaker Pro Advanced, there are Custom Functions which can do this.

But we don't really know what your "condition" is, so can't really say, nor whether it's what you want.

[LaRetta's method is likely what you want; the above is more for a date match; it is what we used to have do before FileMaker 7.]

Edited July 21, 2008 by Guest

[Enuma]

No I dont think it will solve it.

The thing is.

The child table that holds of all my records have two dates (using the drop down calendar).

The purpose of this data bank is focused in these datas.

Each record will always have its 2 dates fullfilled.

I want to make specific records to show in a portal in another table and layout(offcorse)

based in dates that i set. Example:

I want to make it show records between dates that i set like a MIN date and MAX date.

I can make it work fine when only creating relationships basing in one date.

QC_date_min

But i also want a portal to show records that at least one of the dates macth a condition like that one above.

And if i do what i am doing now.

QC_date_min

AND ECD_date_min

No record apear since the dates are diferent and what i want is it show records within a week for example than i have to set my 2 MIN and 2 MAX equaly but it makes the hole sentence false.

[Fenton]

Then you will have to create the return-separated "dates as numbers" in the originating table. You cannot use "<" and ">" operators, just "=". You will be matching lines. It still works fine. It's how we used to do it before FileMaker 7.

But now there are Custom Functions which can produce this multiple line field, from a start and end date (as number). It can be Unstored.

Consider, originating side (calculation, result Text, by Custom Function, using Min and Max dates, as number, unstored):

733240

733241

733242

733243

733244

733245

733246

etc., up to

733260

Target side (2 dates, as number, in return-separated calculation field, result Text, indexed):

733222

733244

It is going to match on:

733244

Edited July 22, 2008 by Guest

[Søren Dyhr]

Although it's correct what Fenton suggests, is there a speed price to pay when using recursive CF's such as this one:

http://www.briandunning.com/cf/8

The problem arises with open ranges both ways - when the range exceeds the CF's iterations max, which can be circumvented via a tail recursion ...but the speed suffers from such extra measures - but when iterations max is reached will wrong results still occure.

This speaks in favor for another approach:

http://www.clevelandconsulting.com/support/viewtopic.php?t=299&highlight=smartranges

Just be careful to obtain the corrected version of the whole! Please notice the linking to Edoshins original text on the matter.

--sd

[LaRetta]

Dang! Couldn't a repeating field be used in recursive fashion here, since it increments by 1 day? I envision Comment's Poor Man's Recursion technique using Get ( CalculationRepetitionNumber ) as iterations ...

Edited July 22, 2008 by Guest

[comment]

Read this several times, and I am still not sure what the question is.

If this is really about setting up a relationship that shows children whose range overlaps the parent's range, then:

Parent::ParentID = Child::ParentID

AND

Parent::Start ≤ Child::End

AND

Parent::End ≥ Child::Start

should do it.

[Fenton]

The problem is that there are 2 child date fields. He/she wants to see matches on either.

I don't think an "open-ended" range is going to be a problem here, nor speed. They clearly want to see dates matching a specified range; an upper/lower limit could be "let" into the calculation of the originating key.

They have not specified anything about the length of the range however, so I'm just assuming.

[comment]

I still don't get it. Match on either of 2 fields wouldn't require < comparisons. I think Enuma needs to put more effort into clarifying the request.

[Fenton]

Not if they were in a return-separated field. Either would match. But it requires a multi-line field on the originating side also, of all "desired" dates. We used to do this in 6.

[Chris Cain]

If (big if) I understand this...

There are two dates in the child table. Given a min and max in the parent table, you want to see records where either date is in the range.

The following method converts dates so you can use text comparisons, comparing 20080722, rather than 7/22/2008. It also relies on a multi-line key on the child side. By adding dates to the child-side key, you could match on as many different dates as you like... i.e. date1 OR date2 OR date3, etc.

In the parent table, create two calculated fields:

"MinFilter" (result is text)=

Let ( [ 

d = MinDate 

] ; 



Year ( d ) & Case ( Month ( d ) < 10 ; 0 ) & Month ( d ) & Case ( Day ( d ) < 10 ; 0 ) & Day ( d ) 



)

"MaxFilter"(result is text)=

Let ( [ 

d = MaxDate 

] ; 



Year ( d ) & Case ( Month ( d ) < 10 ; 0 ) & Month ( d ) & Case ( Day ( d ) < 10 ; 0 ) & Day ( d ) 



)

Now, in the child table, create a single calculation field:



"DatesForFilter"(result is text)=

Let ( [ 

d1 = Date1 ; 

d2 = Date2

] ; 



Year ( d1 ) & Case ( Month ( d1 ) < 10 ; 0 ) & Month ( d1 ) & Case ( Day ( d1 ) < 10 ; 0 ) & Day ( d1 ) 

 & ¶ & 

Year ( d2 ) & Case ( Month ( d2 ) < 10 ; 0 ) & Month ( d2 ) & Case ( Day ( d2 ) < 10 ; 0 ) & Day ( d2 ) 



)

Relate Parent to child where:

Parent::MinFilter<=Child::DatesForFilter

AND

Parent::MaxFilter>=Child::DatesForFilter

HTH

Edited July 23, 2008 by Guest

[comment]

We used to do this in 6.

Yes, but why do it now when < relationships are possible?

[Vaughan]

If you're going to use the "old" way, at least do it right: Smart Ranges.

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

[Chris Cain]

I've attached a sample.

HTH

Cain

TEST.zip

[Enuma]

Thanks Guys The Extensitech method did what i wanted.

Damm a simple OR option in the relation would have done it maybe i should add it in the wish list....

[Fenton]

Oops. I (for some reason) got it in my head that "<" ">" would not work against a multi-line field (because it's multi-valued). But of course it does, as it's just like a relationship's multiple records. Duh.

[comment]

I have seen this method before, so I know it works - but I am still puzzled why. Logically, it shouldn't.

[Enuma]

Hey your method helped me alot, but i dont undesrtands how it worked lol. Would me much of a trouble explaining it to me?

[Chris Cain]

FileMaker allows relationships by a multi-line key, so

value1

value2

in a text field will match fields containing EITHER value1 or value2. This is what we're doing with the DatesForFilter field; creating one field containing both dates, either of which can match.

Also, FM can compare text strings, and sees that A < M, M < N, etc., but with text strings, even if there are numbers or dates, FM compares the first character, then the second, etc, so as text, 100 is less than 99, and 10/1/2008 comes before 9/1/2003, because alphabetical sorting puts a 1 before a 9, regardless of what comes after it.

The rest of the method used has to do with formatting the dates as text, but in such a way that > and < will evaluate correctly. You could just use the numeric equivalents of the date (today is 733247), and this would work most of the time, but it's unreadable. (Also, it wouldn't work for some ridiculously high or low dates, where the number of digits would change.)

Instead, I've changed the dates to YYYYMMDD, which will alphabetize and compare correctly. (Technically, I should have allowed for making the year always 4 digits, in case you're cataloging Roman skeletons, but I felt safe in assuming the year was 4 digits anyway.) 20080724 may not be pretty, but it's easier to understand than 733247.

HTH

Chris Cain

Extensitech, Inc.