Related Record issue

[dcgreco1958]

I have a Service forms database. Each form has a customer and machine serial number field with a value list for the serial number. Currently the value list shows the serial number as the specified field and it shows the serial number for all customers but I would like to know how to filter the pick list to show only the serial numbers relating to the customer on the current form.

Can I do it using the pick list or do I need to create a portal?

 

[comment]

Well, if you have a table of Customers and a table of Equipment, related by CustomerID, and you also relate your Service table to Customers by matching CustomerID, so that you have:

Service >-- Customers --< Equipment

then you can define a value list using values from the machine serial number field from Equipment, show only related values starting from Customers. With this in place, once you select a customer for the service form, the value list will show only the selected customer's equipment.

You will also want to add another TO of the Equipment table and connect it to Service by matching the machine serial number.

 

[dcgreco1958]

Thanks for the reply. My issue is both Customer and serial number reside on the same table. Serial numbers shown are the result of the records created. Can it be done with a value list? 

[comment]

I am not sure I fully understand your structure. If you only have one table where the same customer can have many entries, and you want to see only serial numbers for the current customer, then define a self-join relationship matching on the customer. This will allow you to define a value list showing only values from related records - i.e. values from previous records of the same customer.

However, it is good practice to have a separate table of Customers where each customer has a unique record.

 

[dcgreco1958]

Thank you for your reply. I finally got back to finishing my program and a self-join relationship was the solution I was looking for. It worked perfectly. I just duplicated the one table that has the fields I want to filter to the customer using the customer as the unique identifier. I know I could clean it up with a more specific table but mission accomplished. Thank you for your help!