Filemaker Pro 10 & 11, Windows XP & Vista, Hosted network of 5 users.
National Photographic Company
I have a report that shows when a customer is missing their camera.
The report is based on my CUSTOMERS table and sorts by area and lists all customers per area and then shows the camera serial number from my EQUIPMENT table in a single line portal. This works well as I have a calculation field in my EQUIPMENT table for the camera:
If ( PatternCount ( EQUIPMENT::description ; "Olympus Camera E-1" ) ; EQUIPMENT::serial_number ; "Missing" )
Using this I have a clear "Missing" on the report if there is no camera, this is conditionally formatted to stand out on the report. This all works brilliantly, thanks to help from this forum.
The problem I now have is transferring this data to another table. I have a system status screen that shows data from many tables, I am using portals to give an overview of our status. I would like to include a portal that displays these missing cameras without printing the report.
CUSTOMERS and EQUIPMENT are related by a unique customer_id that matches a location_id, and this is the relationship the report is built on.
I now have a STATUS table that the status screen is based on with a global field to link it to all records in CUSTOMERS. When I create a portal from customers and include the calculated camera field from equipment, only some serial numbers appear and most are 'missing'. I tried another table occurance of equipment but it made no difference.
No matter how I try, it will not reproduce the same results as the report does, it seems as if it only sees the camera if it happens to be the first record for the customer, if a lens is listed first it displays 'missing'.
I then created a calculated field in CUSTOMERS to duplicate the camera field in EQUIPMENT but this also gave misleading results as above.
I must be missing something simple so I'm hoping someone can help me. Eventually if it works I'll be filtering out the numbers so the portal will only show missing cameras, alerting staff to replace it, so it would be a useful tool for us.
Thanks in advance