CODEWITHSUNDEEP
macosrecordlookupfilemaker
user3438247
user3438247

Reputation: 1

Lookup record from related table

I can't for the life of me figure out how to do this. It seems like a feature I've seen before using a tutorial, but I can't find it in any searches or books I've went through. There's a possibility I'm just not using the right keywords.

Anyways, what I'm trying to accomplish in Filemaker is I have a related table. So for example I have a Customer and Invoice table that are both related to each other. Now on the Invoice layout I want to relate a record to the customer table. I know I can do this by copy and pasting a serial key, but I don't want to lookup the serial number.

What I want is a button that when I click it, it pops up a window of all the customers I have in the database and I can then click the customer I want and it will then fill in that Serial field for me along with any other accompanying fields such as the Name and Address.

Hope I was clear in asking my question and I look forward to hearing your responses. Thanks for all the help :)

Upvotes: 0

Views: 81

Answers (2)

VikingBlooded
VikingBlooded

Reputation: 859

optimally, you're probably clicking a button on the customer layout that performs a script to create or view an existing invoice. If so, capture the customer's ID in the script and set it when the invoice is created. No more need to do the manual linking. Also, if you create the related record from a portal, the ID of the parent will automatically be inserted. Otherwise, you will have to use one of the options Justin posted which is more prone to data entry errors. If you want it directly on the invoice layout, use a value list, the problem here is that, if your customer list gets too long, the valuelist will be a hassle to sort through and you can only display certain information there.

You could also create a button on the invoice layout that pops a new window, takes you to a report layout that list all customers and associated data. Create an empty field that covers the row of data (trasnparent) and attach a button to it to grab the selected customer and pass that back to the script creating the invoice. This way, you could see all customers and any data that might need to be seen to differentiate them.

Upvotes: 0

Justin Brown
Justin Brown

Reputation: 61

It sounds like you have an ID for the customer that isn't necessarily their name but rather a number that is auto-incremented. If that is accurate you can use a three part combination to tackle the problem. There may or may not be suitable tutorials for this already:

  • drop-down list that chooses from a value list
  • value list that selects from the ID number field in the related table
  • additional layout field using "Display data from" the friendly name in the customer table

To start you add your drop-down list. For Values from create a value list with "Use values from field" and select the related field. Play with "Also display values from second field" to get the look and feel that works best. This screen capture gives you an idea of something similar:

ID and friendly name drop-down list

What you see in the screen capture is a drop-down list that captures an ID value from the related table, but in doing so displays the friendly name. Once you have a working drop-down list you can add you additional field, specify the related table and friendly name field. This layout field will be overlayed atop the ID field in order to mask it. On screen and in print you only notice the friendly name.

From there, simply add the other fields from the related table and enjoy the time savings of easy Filemaker layouts.

Upvotes: 0

Related Questions