2 DBs linked by 4 relationships (need to lookup by 2 too?

[Islandtraveler]

HELP... I have 2 databases and they are linked with 4 relationships (A=A and B=B and C=C and D=D)

If all 4 are filled in no problem it matches the records BUT if only 2 of the 4 relationships are filled in (other two are blank in both DBs) then it can't find the records??

How can I set it up to realize 2 are blank but they match and the records are looked up?

Thanks

[Vaughan]

Please explain why there need to be four relationships, and why some of the fields can be left blank.

[Islandtraveler]

Thanks...

I am tracking an agency that has 4 levels of a department, Ministry - Bureau - Division - Program

There are goals for each, sometimes they want a goal for the Program level (using all 4 fields) and then at times they want the top levels only (which do have seperate goals too).

I have the main DB that if you find using all four M-B-D-P it brings over information relating to that. BUT there are some goals that only relate to the top level so I do a find on just the M-B (the other field are empty. But because I told the DBs to relate all four fields to match it does not bring the related records that only have the two fields filled in?

Does that help?

Thanks

[Søren Dyhr]

I wonder if you mean something in the vicinty of this:

http://fmforums.com/forum/attachment.php?attid/3285/

--sd

[Islandtraveler]

Well it does look the same on the relationship section (3 compared to my 4) BUT doesn't quite address my problem.

If I do a find with only 2 of the 4 fields linked to the other database it just doesn't get the related records from database 2.

Even if the other 2 fields are empty in both databases?? I can't figure out how to build the relationship to allow it to know if the field is blank to ignore it? The options are basic = less than greater than etc.

Any clues

*My quick fix was to add an "n/a" in the fields I am not using for that record but I would like nothing to be in the field if I am not using it...

Thanks for your time

[Søren Dyhr]

If you have ValuelistItems filled in behind the scenes will it act as the key fields instead of the ones the user put's something in. 4 empty fields gives four filled keyfields with all availiable key values.

Check out the red X'es in my template they clear the fields, and makes the ValueListItems( start working.

--sd

[Islandtraveler]

Thanks for the info and temp... I need to let this soak in and will take a deep look at it in the morning! It's 1:30am here and I'm a bit in a daze.

Thanks for the input and samples, I'll see if it does it for me tomorrow.

Take care