Create table occurance using OR instead of AND...???

[angelleye]

I'm still getting used to the way FM uses "table occurances" which is very different from other database packages I've worked with. Seems a little strange to me that the same table can be in there multiple times with different names...but that's not really what this post is for.

What I'm trying to do is relate tables with an OR statement instead of AND. I can do this very easily via ODBC with web scripting such as ASP. However, within FM this doesn't seem to be possible unless I'm just missing it, which I hope is the case.

An example is as follows: I have a table called Employees that houses basic data for each employee here. I've created another table in FM called Assignments that I am using for a custom task manager.

The Assignments table has fields for an Assignee as well as a CoAssignee. These fields simply take the full name of the employee that you're assigning that task to. This full name field is used to relate back to the Employees table.

The idea is to be able to look at your Employee record and get a list of all current assignments that have been assigned to you. This needs to show assignments that have you as the Assignee and also ones that have you as the CoAssignee. With ASP in a web script I could simply use the SQL:

SELECT * FROM Assignments WHERE (Assignee = 'Employee Name' OR CoAssignee = 'Employee Name') AND Status = 'Open'

This would return any records that have a status of Open for that employee where they were the assignee OR the co-assignee. Pretty straight forward.

In FM, however, I have to create table occurances for these. I don't see an option for OR when adding multiple relationships to a table occurance. It just puts AND and I see no way to change that. As such, I was forced to create 2 seperate table occurances: CurrentAssignments, which uses the relationship Employees::FullName = CurrentAssignments::Assignee. Then I had to create another table occurance for CurrentCoAssignments using Employees::FullName = CurrentCoAssignments.CoAssignee.

Is that the only way I can make that work or is there indeed a way to use OR in relationships/table occurances in FM?

Any information I can get on this would be greatly appreciated. Thanks!

[David Jondreau]

It's true. There's no "OR" function for creating relationships. However, you can set up an "OR"-like relationship pretty easily because an FM relationship sees each value in a return delimited list as enough to set the relationship.

So create another field of type Calculation in Current Assignments, and name it, say, AllAssignees = Assignee & "¶" CoAssignee

Then Employees::FullName = AllAssignees should do what you want.

Also see HERE for a look at TOs versus tables. It is confusing...until it isn't.

[angelleye]

Hmmm...I normally try things before I respond but I'm a little confused by this...

If I try that method and I end up creating a new assignment with both an Assignee and a CoAssignee then the resulting AllAssignees calc. field would get:

Fred Jones

James Smith

But then, if I have other assigments for Fred Jones without any CoAssignee would they still show up in Fred's list? "Fred Jones" does not equal "Fred Jones¶James Smith" so it seems to me that with that relationship I wouldn't get everything I need. Unless....does FM basically use a SQL equivalent of LIKE in its relationships? If so, that seems rather odd.

I'm gonna play around with that anyway but wanted to respond with my thoughts. thanks for the response!

[David Jondreau]

I'm not familiar with SQL, but FM does see each value (value being "¶" delimited) in a relationship independently.

So "Fred Jones" = "James Smith¶Fred Jones" will test true in the relationship.

To be really clean, I hereby revise my original calc and make it =

Assignee & Case( not isEmpty(Assignee) and not isEmpty(CoAssignee); "¶") & CoAssignee

though as originally posted it should work fine.

[angelleye]

hmmm...I went ahead and tried it but it causes all of my assignments to disappear from my list in Employees. I added the calc. field to Assignments and added it to the layout to confirm it indeed holds the value of:

Employee Name1

Employee Name2

I then changed the relationship in Employees from Employees::Full Name = CurrentAssignments::Assignee to Employees::Full Name = CurrentAssignments::AllAssignees

Upon doing so all of the listed assignments in the Employee Name1 record in Employees disappears.

[angelleye]

On another note, why does this forum add its own spaces in stuff I type like that!?

[David Jondreau]

Make sure the calculation result is "Text" (not the default number) and that the calc is NOT set to "Do Not Store..."

[David Jondreau]

Dunno about the spacing on the forum, it happens to me too. I find it harmless, slightly annoying, or very annoying depending on the situation.

[angelleye]

Ah, yeah I did forget to change to text. Had to shut out of FM for now, though. I'll play with that later tonight and update ya. thanks!

[angelleye]

Works great. thanks!!!

[Søren Dyhr]

It's true. There's no "OR" function for creating relationships. However, you can set up an "OR"-like relationship pretty easily because an FM relationship sees each value in a return delimited list as enough to set the relationship.

almost correct:

http://www.fmforums.com/forum/showpost.php?post/250204/

--sd