how to simulate an OR non-equijoin?

[Owen Mathews]

Once again my programmer's brain has no trouble with how to go about this using pseudocode or SQL statements, but get me into FMP7, which is still new to me, and I'm lost. I poked around the forums and found ways to simulate an OR equijoin, but I'm dealing with inequalities.

I have two tables: Incident and Owner (there are more, but these two are sufficient for illustration). Incident records a problem with a computer, and has an OpenedDate field. Owner records an owner of a computer, and stores three fields relevant here: StartDate (when the owner received the computer), EndDate (when the owner turned it in), and Current (flags which ownership record is current for a particular computer). For example, if I search the Owner table for Current="yes", I get a list of all current computer ownership. If I search Owner for Name="Steve", I get a history of all computers owned by Steve, hopefully no more than one marked as current.

From a layout based on the Incident table, I want to view the owner of the computer at the time the incident was reported. I see two kludge solutions and one ideal one, and of course it's the ideal one that I can't seem to implement in FMP7.

Kludge 1: Use a sentinel value for the end date, don't use Owner's Current field at all. If I set the EndDate field to 12/31/4000 (the max allowed date) to indicate that this ownership record is current, then I can easily implement an AND join:

Incident::OpenedDate >= Owner::StartDate AND Incident::OpenedDate <= Owner::EndDate

I don't like this kludge because I'm a programmer and it's just a little inelegant. I mean, why code a solution that's just going to break in another 2000 years? I know, hubris. That's what makes us programmers so great. ;-)

Kludge 2: Don't make the view of the owner of the computer live from the Incident table. When you create a new incident record, just copy the current owner into a field in the Incident table and be done with it. Obviously this is information duplication and again my programmer brain shudders at the thought.

Of course what I see as the "proper" solution (where gCurrentStatusYes is a global field set to "yes") is:

Incident::OpenedDate >= Owner::StartDate AND (Incident::OpenedDate <= Owner::EndDate OR Incident::gCurrentStatusYes = Owner::Current)

So, is there anything I can do to accomplish this, or am I relegated to kludging it?

[Søren Dyhr]

I came to think of smartranges with the unequal operator... hardly elegant, but a solution??

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

or

like Nightwing does it with a custumfunction in...

http://www.nightwing.com.au/FileMaker/demos7/BookingSystem.sit

--sd

[Ender]

You might try building the OR condition into the match key on the Owner side. Something like:

EndDate Match (calculation, date result) = case(isempty(EndDate); Date(12;31;4000); EndDate)

Then use that as the match key in the relationship:

Incident::OpenedDate >= Owner::StartDate

AND Incident::OpenedDate <= Owner::EndDate Match

[comment]

I am not sure what exactly the Owner table is - do you have a ComputerID in there? It sounds like a join table between People and Computers (perhaps a better name would be Ownerships, or Assignments?).

Anyway, if I am guessing correctly, you could try a relationship:

Incident::ComputerID = Owner::ComputerID

AND

Incident::OpenedDate >= Owner::StartDate

This relates all previous owner(ship)s up to and including the date of the incident. Sorting the relationship by date, descending, will cause the owner at the time of the incident to be the first related record.

[Owen Mathews]

S

[comment]

as soon as I reassign a computer, any incidents that were opened under the previous assignment will suddenly change

I don't think so. The relationship cuts off any assignments made AFTER the incident date. The last assignment made BEFORE the incident will always be the first related one.

Just to clarify - you need two relationships: one is for identifying the owner at the time of the incident. Another for identifying the current owner. This can be as simple as:

ComputerAssignment::ComputerID = ComputerAssignment 2::ComputerID

And again the relation is sorted by date.

This way, you don't even need the EndDate field. You simply fill out a new ownership record, and all the previous records become obsolete automatically.

[SlimJim]

Assuming the end date field is not used for any other purpose, and using the "end of days" value would imply that, why not simply put the incident date into the end date when an incident happens. This has the advantage that the end date can then be used to test for incidents when the computer is reassigned and removes the necessity for an OR since the test becomes a simple date range test.