2 Replies Latest reply on Oct 21, 2015 12:10 PM by eyee12

    How to Pull Records from One Table, and Field Inputs from Another


      Hi all -


      I'm pretty new to Filemaker, yet I've watched videos on working it and have what I initially want built out. I'm just a bit stuck at this one part though. A little background: I'm doing a database for a casting company and I have a layout for a Job Info and Talent. Job info Layout has fields such as Job Title, Production company, job info, and Roles.


      I want to be able to input all the job information on this Job Info layout and from there, I want to be able to input all the Roles for the job. Anyways, where I'm stuck is I essentially want to click a button for Each Role that's inputted to cast from the Talent table. i.e. Role 1 - Hipsters, Role 2 - Business, Role 3 - Athletes, etc. I'll click a button and it'll take me to my Talent table where I can use the "Find" function to search talent that fit the qualifications.


      When I tried, I have the associated table being "Talent". That way, I can click the button to get to this layout, and I can see all my search criteria listed so I can filter and find the talent I need for the specific Role. What I also need on this sheet, is for it to pull the data that I inputted into my Job Info layout such as Role, Job ID, Job Title, etc. It seems that I can either do one or the other but I can't seem to combine the two together on one layout.


      I hope this is not too confusing. Here's a look at what My Relationships look like and also a screenshot of the Layout I have set up, but not working properly yet. Thanks for any help!!


      Screen Shot 2015-10-21 at 9.58.52 AM.png


      Screen Shot 2015-10-21 at 10.01.04 AM.png

        • 1. Re: How to Pull Records from One Table, and Field Inputs from Another

          The issue is that there is no inherent relationship between the Job record based on which you want to search/filter, and any Talent record.


          But you can add one:


          create a global field

          with the same data type as your Job table primary key; say, gCurrentJobIDForSearch.


          create a value list

          “using fields”: Job::id (primary key), Job::description (something human readable and unique**), and check “Show values only from second field”.

          create a relationship

          from Talent to a new table occurrence of Job, say, Job__forTalentSearch:

          Talent::gCurrentJobIDForSearch = Job__forTalentSearch::id


          on your Talent list layout, add a header part

          place the gCurrentJobIDForSearch field there and format it as a popup with the new value list.

          Since now there exists a relationship, you can place any fields from Job__forTalentSearch on that layout. Use the popup to select the desired Job record; not only can you see its settings, you can change and reference them in scripts etc.).

          (Next step: select Talent records, and when you're done, use a script to create records in Job/Talent, based on your selection and the selected JobID.)

          You could also use a script to initialize searching for a Job from Job, like

          # [ on Job layout ]

          Set Field [ Talent::gCurrentJobIDForSearch ; Job::id ]

          Go to Layout [ TalentSearch ( Talent ) ]

          # [ layout will already show the settings for the Job record you just came from ]


          **If necessary, create a calc field, type text, in the Job table like description & " (" & id & ")"

          and use that as second field

          • 2. Re: How to Pull Records from One Table, and Field Inputs from Another



            Thank you SOO Much!! It took me a while to read and comprehend what you were doing with that. But, this worked PERFECTLY!


            One other question if I may ask you is.. now that I'm searching for these talents based on some criteria, It automatically fills in the rest of the fields as soon as I do a search. Here's a video of that happening. i.e. I'll search for people that have Bartending skills, but then it automatically fills in Age Range, Status, Body Type, etc. based on the First record.


            Here's a video of it happening: Casting