Relationship Advice Needed :)

[David Benedetti]

Hey all,

I'm using FM Pro 8.5 on WinXP.

I'm making a training documentation database, and I've been having a bit of difficulty figuring out how to accomplish what I want.

I have a database of users, documents and requirements.

Users are assigned a GID (group ID) based on their department, the requirements database has 2 columns, GID and SOP (document). Each line is broken down like this: GID has a list of group IDs that have a certain document seperated by 2 colons as a requirement: Ex: MFG::FAC::IT The SOP field has the document number in question.

My main question is how do I get FM to search for only partial selections in the GID (If user ID is FAC and the requirements GID string is MFG::FAC::IT it will still return true.

Any help that can be given would be most appreciated. If I"m not being clear enough in what I'm asking please let me know as well, so I can attempt to better explain it.

Thanks!

[MarkWilson]

In the help file, do a search for "find." It will show you how to use wildcard characters in your searches.

[David Benedetti]

Hey, what's up?

Thanks for the advice, but I'm not sure that it's applicable to my situation.

How do I specify the wildcard character in my related tables? it's set so the relationship is User.GID = Requirement.GID

[Vaughan]

Wildcards cannot be used in relationships.

I think your best bet is to separate the FAC and GID and SOP values into separate fields. Then use muti-predicate relationships to filter out those rrelated records you want. Multi-keys can be very powerful here.

[David Benedetti]

Ok, I think something is getting lost in the translation, so let me try to reexplain myself, and I apologize for any confusion.

Here are the tables I have:

Table: USERS

------------

UID | GID

------------

001 | MFG

002 | FAC

003 | MFG

------------

Table: REQUIREMENTS

----------------------------------

SOP | GID | FLAG

----------------------------------

5001-MF0 | MFG JIT FAC | CORE

5002-QA0 | QA QC MFG | CORE

5003-MF0 | MFG JIT FAC QA | CORE

Table: MAIN

---------------------

UID | SOP

---------------------

001 | 5001-MF0

Table: SOP

--------------------

SOP | Title

--------------------

5001-MF0 | My SOP Title

and their current relationships:

MAIN.UID=USER.UID

MAIN.SOP=SOP.SOP

USER.GID=REQUIREMENTS.GID

Basically what I want to do is show if an SOP is a core requirement based on group. IE: on the MAIN table, 5001-MF0 would be a core document for USERS.UID 01 since MFG=MFG.

Can anyone help me with this? Thank you so much in advance, and again, sorry if there is any confusion.

[Vaughan]

The requirements table has multiple GID values in the one field. This isn't optimal. They should be split into multiple records.

However as a multi-key it might work: separate the GID values with paragraphs.

[David Benedetti]

Hey, thanks, I'll try setting it up as a multi-key and let you know how it works.

I could set them each as an individual key but the problem is that some of the SOPs are requirements for multiple groups, so I'd like to make each SOP 1 record as opposed to 4 or potentially more.

[David Benedetti]

We are happy : Multi-Keys to the rescue!