one option (there are others) would be to use a filtered portal to your phone numbers on a contacts layout. Your portal filter expression can limit the related records to just the "work" number or just "Work", "Work Fax", "Home", or "Mobile".
Hi - thanks for the response - I have figured out how to 'portal filter' the individual phone types - this seems to work best on a form/layout page. I have to say, it seems a VERY awkward way to filter a data set.
HOWEVER - this does not seem to be possible in a report layout, such as a label page or a form letter. So far as I have been able, the 'portal filter' field does not seem to be possible in line with report text, and therefore, floats around the layout. Isn't there some way to filter the related Child table data set?
Thanks again in advance,
Another thing I've tried to 'filter' or limit the data set, was to setup a Child table, in this case called 'PhoneW' and a second, new table called 'PhoneType' that has only two fields, an ID field and a Type field. I then created a data set in this new table with only one (1) entry - in the field Type, an entry 'Work'.
I then related the 'PhoneW' table to Contacts by the ContactID, and related by way of a second join, the 'PhoneType' table by relating the Type field to the Phone field. Understanding how these data sets work, that should have limited the data set to only those phone numbers that had 'Work' in the Phone field of the PhoneW table. It did not - it still resulted in showing all of the related types of phone numbers for the related Contact.
Just an FYI - can't imagine a crazier way to have to do something....but I tried that as well, which should have been a good work-around - no joy.
this does not seem to be possible in a report layout,
Please describe your report layout. This method should work just fine on either a form or list view layout. It shouldn't "float" anywhere and I'm not sure what you mean by that description.
If you defined a calculation field, constHome in Contacts with this expression: "Home".
Then you could add another occurrence of the Phones Table Occurrence to your relationship graph to produce this relationship:
Contacts::ContactID = Phones|Home::ContactID AND
Contacts::constHOme = Phones|Home::Phone
Then fields from Phones|Home can be added to a Contacts layout to show only data for the Home phone number. But note that this requires one such calculation field and added table occurrence for each phone type. I suggested filtered portals as an alternative that avoids that clutter in your relationship graph.
A third option is to set up a calculation field using ExecuteSQL to reference records in the related table that are only of a specified type or group of types. This option avoids portals and changes to your relationship graph, but requires that you have a decent working knowledge of SQL and how to use it with this function.
Hi, I've taken a stab at the ExecutSQL approach.
I created a calculation field on my contacts table, unstirred, where the calculation is as follows:
ExecuteSQL ( "SELECT phone.Number FROM contacts JOIN phone on contacts.ContactID = phone.ContactID WHERE phone.Phone = Work:"; "" ; "" )
It returns a ? - which I understand is an error indicator.
Could it be my limiting parameter - phone.Phone = Work: - is defined incorrectly. Work: is the actual field entry.
enclose Work: in single quotes:
WHERE phone.Phone = 'Work:' "
useful SQL resources for FileMaker:
SeedCode's free SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
FileMaker's reference Document on SQL (also applies to other uses of SQL in FileMaker): https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
Thank you....this should work, but, when the layout tries to load, I simply get the 'spinning color wheel' - the database is large - 17,000 contacts - but it seems to be stuck. I've let it run a while, thinking it was calculating 17,000 field calculations - is it possible that this will hang the calculation field?
I did a contacts search for one surname, which resulted in 14 contacts and then changed to the report layout where I've inserted the WPhone field, thinking to test whether or not it was a large database calculation issue. It still seems to hang with the spinning color wheel.
I may have to go with you second suggestion above - .......
I used your second method above - being to setup a calculated field 'with this expression: "Home"'... etc.
You are correct it's a messy outcome for my table schematic, but, it did work!
For now I'll use that, and try to debug my SQL approach.
The filtered portal option would seem much simpler as long as you only need to "display" that data and not access it in scripts or calculations.