Jump to content

Recommended Posts

  • Newbies

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?

 

Link to post
Share on other sites

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.

 

Link to post
Share on other sites
  • Newbies

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? 

2081397916_ScreenShot2020-04-20at2_08_15PM.thumb.png.0915d7374c518ef004f48e2c1f90971d.png1257445154_ScreenShot2020-04-20at2_07_29PM.thumb.png.48d693d0c9852a82dcf55673366ff54f.png

Link to post
Share on other sites

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.

 

  • Like 1
Link to post
Share on other sites
  • Newbies

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!

141763870_ScreenShot2020-04-23at10_08_23PM.thumb.png.928e6ea79491c45995a591a2b386e700.png

 

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.