Thank you for your post.
If you are trying to only show "Olympus E-1 Camera" in the portal, then make sure the relationship includes "Olympus E-1 Camera". It sounds like it is showing all equipment.
If you also want to show all equipment, then add a second table occurrence and build the relationship based on "Olympus E-1 Camera". Then, the portal will only show that entry and not the other equipment.
Thanks TSgal for the quick reply.
I'm not sure of how to do what you say though."OLYMPUS E-1 CAMERA" is an item in my description field of Equipment table. the table is related by a customer id, so we can move equipment between Customers. I'm not sure how to include the camera in the relationship as there are many other descriptions.
We are trying to find out which camera each customer has by serial number, and if there is no camera there we want to reflect that by a blank or text message against that customer.
i.e. <customer id><customer name><equip. description(camera only)><equip. serial number(camera only)> (id & name from customers table, description and serial number from related equipment table. Relationship via customer id.)
The above line appears for each customer in the report. I have sorted records by"OLYMPUS E-1 CAMERA" and if there is a camera it works fine, however if no camera is at that customer it picks the next equipment description and serial number available.
Should I set up a new relationship? how can I isolate the camera in this relationship from other items in the description field.
I have also tried creating a lookup field in the customers table to get the camera serial number with little success. Both lookup and calculation auto entry seemed to have difficulty, but that may be in my syntax or wrong function use.
Yes, you should set up another relationship, using a new occurence of the Equipment table. Something like:
Customers:: CustomerID = Equipment 2:: CustomerID
Customers:: gDescription = Equipment 2:: Description
If you enter a description (e.g. "OLYMPUS E-1 CAMERA") into the global gDescription field, the relationship will show only equipment that matches the description.
You've spelled out a lot of complications in your design that make it difficult to suggest improvements. You could put a calculated ID field in your equipment table that works something like:
If(patternCount(descriptionfield, "camera") > 0, customerIdfield, "")
Use that field as your key field in your relationship and only records that contain "camera" in the description field will appear in your portal.
Given the other information in your post, it may be that a significant redesign of your database tables and relationship might be in order.
Brilliant, works a treat.
I agree my DB is a bit convoluted but I'm not a developer and my company wont pay for one so I'm struggling through. I am planning a clean up with a second version once I have my relationships right. thanks for your help, and you'll no doubt hear from me again.
yes thanks that does work, but I think I'll do it the way PhilModJunk suggested. i appreciate your help.