No "OR" Relationships? Way around?

[Joe S.]

As in the last post I read, nearly complete n00b; pretty good at Access a few years back, but now just using FMP for my own things.

I'm a music teacher, and I have a FMP8.5 database of my students' info and our instrument inventory. I use the serial numbers from the Inst. table as the autocomplete list for the Inst. Assignments on the student records. Some students borrow more than one instrument, so there is a field for Inst. 1 and Inst. 2.

Now, I'm trying to get the Instrument records to show the student names that they're associated with.

When I link Inst. 1 to the Serial #, and add a single-row portal on the Instrument page to the student name, I see the name I want. However, as expected w/ AND relationships, if I link Inst. 1 and Inst. 2 to Serial #, I get nothing.

Each instrument can only be associated with one student (by serial number), but there are two possible fields in the student database. I want to view the student name whether the serial number is in Inst 1 OR Inst 2.

I'm sure the answer is very easy, but I've run out of patience trying to read help files and forum posts. I haven't done this in awhile (like 8 years), and if I were in Access, I'd probably base it on some query. Please help!

[GuruDrew]

Make a field in the Assignments table called BothInstruments. Make it an autoenter calculation of Instrument 1 field and carriage return and Instrument 2 (Instrument1 & ¶ & Instrument2 ). Allow it to replace existing value. Then relate Instruments to Assignments on Instruments::Serial Number to Assignments::BothInstruments only. You'll have to replace the new BothInstruments field in the assignments table with the same calculation to get historical data up to date.

[Joe S.]

Thanks! The first time I tried it, I got in the name portal, but I started over and now it's ok.

Although this is now off-topic, is there an easy way to pop up a message when a serial number is entered that is already in use that is already in use on the student page. (maybe it could even say who it's in use by...)

At this point, just to be sure I haven't double-booked a particular instrument, I go to the table and make sure the same number doesn't show up twice. I'd need the ability to override this though in some circumstances. Thanks again!

[GuruDrew]

Make a self join relationship from your Assignments table from BothInstruments field to BothInstruments field. Call the new TO UsedInstruments. Then in the Assignments table put the following validation calculation on the Instrument1 field: If ( Position ( Evaluate ( Instrument1 ) ; List ( UsedInstruments::BothInstruments ) ; 1; 1) > 0 ; 0 ; 1 )

Instrument2 field validation is much the same, just change Instrument1 field to Instrument2. You can then pop up a nice message saying who the student is. Allowing override on the validation should give you just what you want.