3 Replies Latest reply on Nov 19, 2015 6:45 AM by njudson6

    Pulling Data from Table-VLookUp?


      I have stressed out about this situation enough and am finally turning to you guys for some help.

      I have a database for my print estimating. I have two table "In House Jobs" & "Paper Catalog"

      I have a field with a dropdown list on the "In House Jobs" Table that pulls form the "Paper Catalog" Table. When I select a paper, I want it to pull the cost of that paper from the "Paper Catalog" and place it in my Cost Per Sheet field in the "In House Job" Table. Any assistance would be greatly appreciated. I have succeeded in doing this in Excel through Lookup, but FMP doesn't have that function.


      Thanks in advance!

        • 1. Re: Pulling Data from Table-VLookUp?

          Where is the cost being used? Do you just need to see it or is it used in a calculation somewhere?


          It sounds like you are pulling in Paper with a value list and setting a field on the Jobs table. Will this always be one Job one paper type? If so, you could have a cost field on the Job that is set by script or calc. Which way is up to you. If there is no relationship I would use ExecuteSQL ( "SELECT cost FROM paperTable WHERE papertype = ?"; ""; ""; JobField) [rough syntax here but you get the idea]. You can use it directly in a calculated field or in a script to set a field.


          If there are multiple paper types per job you may want to use a portal. This could be setup any number of ways.

          • 2. Re: Pulling Data from Table-VLookUp?

            FMP most definitely has that function. In fact, it has a few different variations on that theme.


            In Excel, for a vlookup, you need lookup_value, table_array, Col_index_num and Range_lookup.


            In FileMaker, first create a relationship between the table you are in (In House Jobs) and the table you want data from (Paper Catalog). Create the relationship by linking the field holding "lookup_value" in this table (the paper field where the user selected from a drop-down) to the field that would have been "column 1" in the "table_array" of the other table (the field in Paper Catalog where that paper field would match).


            Now the options begin.


            In In-House Jobs you want to see "Cost" from Paper Catalog, so you create a field called "Cost". The closes thing to an Excel vlookup would be if you made "Cost" a calculation field, equal to Paper Catalog::Cost. In FM, for this functionality, you don't even need to create a field. You can actually show Paper Catalog::Cost on your In House Jobs layouts, or refer to that field in your IHJ field calculations.


            Another thing to consider is that when the cost changes in Paper, you probably don't want it to change for prior jobs. A common way to get around this in a spreadsheet with vlookup is to calculate the vlookup, and then copy, paste special, values so that the function doesn't keep evaluating new costs. In FileMaker, create a Cost field in IHJ. Make it a number, and in the options, for auto-entry, select "looked up value" and select Paper::Cost. When the user selects paper from the drop down, the cost will be copied from Paper to IHJ, but then FM won't do it again unless the user changes the field(s) involved in the relationship, namely your drop-down field, again.


            These two methods do the bulk of the work you would do in Excel with vlookup. There are still other methods of getting matching data from a related record, including a function called "lookup", but these account for most of the scenarios.


            So, create your relationship (if you haven't already) and decide whether you want the "vlookup" to be dynamic (keep up with the newer costs) or static (fetch once). If the former, just show the related cost field. If the latter, create a cost field to copy the value into with an auto-enter lookup.




            Chris Cain


            • 3. Re: Pulling Data from Table-VLookUp?

              You are a genius! Thank you so much! I knew there had to be a way, I just needed it written out. Appreciate the help!