9 Replies Latest reply on Apr 16, 2009 1:54 PM by donjuancarlos_1

    FM10 - Perform lookup on unrelated database

    donjuancarlos_1

      Title

      FM10 - Perform lookup on unrelated database

      Post

      Hi all,

       

      This is my first post here. :)

       

      I am creating a database to allow my company's quoting department to perform quotes in FM10.

       

      I have created dropdown menus for each quote line and populated them with value lists from my unrelated inventory file.  For example:

       

      Inventory item                                                     Unit Price                Quantity               Total

       

      [dropdown list showing door types]                    $x.xx

      [dropdown list showing window types]

      [dropdown list showingdoor hardware types]

      .

      .

      .

       

      When I select a door type from the dropdown list, I probably need some sort of script trigger to go to the inventory database and find the associated unit price and automatically enter it in the Unit Price box. Does anyone know how I would do this?

        • 1. Re: FM10 - Perform lookup on unrelated database
          philmodjunk
            

           

          I am creating a database to allow my company's quoting department to perform quotes in FM10.

           

          I have created dropdown menus for each quote line and populated them with value lists from my unrelated inventory file.  For example:

           

          Inventory item                                                     Unit Price                Quantity               Total

           

          [dropdown list showing door types]                    $x.xx

          [dropdown list showing window types]

          [dropdown list showingdoor hardware types]

          When I select a door type from the dropdown list, I probably need some sort of script trigger to go to the inventory database and find the associated unit price and automatically enter it in the Unit Price box. Does anyone know how I would do this?

           

          Well yes, but are you sure you really want to do this?

           

          On of the database systems I created and still maintain is an invoicing system for a scrap metal dealer. He buys from customers where you sell, but the unit price issue is the same.

           

          In my database, the user fills out an invoice by selecting a type of scrap metal from a drop down menu. The unit price is automatically looked up from a related material table. This action copies the price from the material table into a line item. Since the price is copied, if the boss chooses to change the price, that change will affect future invoices but will not affect invoices that have already been created.

           

          Does this sound like what you need?

           

          If so, let me know and I'll post back a simple description of the technique.

           

          • 2. Re: FM10 - Perform lookup on unrelated database
            donjuancarlos_1
              

            Yes, I definitely need a lookup so the price in the quote doesn't change when the price in the inventory database does.

             

            The issue I have is that I have approximately 50 lines on this quote form and the order/structure of the quote form needs to stay consistent from one quote to the next. So every line is unique.  Under these constraints, I don't know how I would relate the two databases (like an invoice with inventory parts). I suppose it's possible to relate the Item Name field in the Inventory database with every Inventory Item field in the quote database.  I just don't know where I would go from there.

            • 3. Re: FM10 - Perform lookup on unrelated database
              philmodjunk
                
              donjuancarlos wrote:

              Yes, I definitely need a lookup so the price in the quote doesn't change when the price in the inventory database does.

               

              Let's see if I can speak to both issues. In this post I'll describe Implementing Lookups from a related table and  I'll describe using a script trigger to look up data from an unrelated table in a following post.

               

              Looking up data from a related table.

              I suppose it's possible to relate the Item Name field in the Inventory database with every Inventory Item field in the quote database.  I just don't know where I would go from there.

               

              "....every inventory item field..." That looks like the key problem. You should have just one field in each table to use as relationship keys.

               

              Ideally your inventory table should have the following structure:

               

              ItemID: number

              Quantity in: number

              Quantity out: number

              On Hand: Quantity in - Quantity out

              Total: Summary field (Total of On Hand)

               

              You should also have a second table let's call it "Catalog."

              ItemID: Auto-entered serial number 

              ItemType: Text

              Description: Text

              Price: Number

               

              Then you have your invoices table:

              Invoice Number: auto-entered serial number

              Date sold: date field

               

              (other fields to identify customer, compute sales tax, shipping info, etc.)

               

              Finally have a table called LineItems

              Invoice number: number

              ItemID: number

              Qty: number

              Unit Price: number (define as look up from catalog)

              Line total: Qty * Unit Price

              (Plus other fields depending on your business model)

               

              Then establish the following relationships linking your table:

               

              Invoices::Invoice number --=-- Line Items::Invoice Number

               

              Line Items::ItemID --=-- Catalog::Item ID

               

              Inventory::ItemID --=-- Catalog::Item ID

               

              With this design you can define the auto enter options for Unit Price to use the 2nd relationships (Line Items to Catalog) to look up the current price. You can use the ItemType field to group materials for reporting and to implement shorter value lists for your drop down menus. You use the invoices to line items relationship to pull together all "50 lines" of your quote page on a single layout--either through a portal or by creating a list view base on Line Items with the Inventory fields in a Header and Footer.

               

              That's how I would do it, but you asked about setting this up for an unrelated table so I'll describe that in a following post.

              • 4. Re: FM10 - Perform lookup on unrelated database
                philmodjunk
                  

                Ok, this is what you asked for: Using the contents of a drop down menu to search a second table and return the result back to a field in the original table:

                 

                I'm calling your menu formatted field "Item" and am assuming it uniquely identifies one record in the inventory table.

                 

                Trigger Script:

                 

                #Pass field contents as a parameter to the search script

                Perform Script["Price Search", Parameter: Invoices::Item]

                Set Field [Invoices::Unit Price, GetasNumber(Get(ScriptResult))]

                 

                 

                "Price Search" script:

                 

                Enter Find Mode []

                Set Field [Inventory::Item Field, Get(ScriptParameter)]

                Set error capture (on)

                Perform Find

                Set error capture (off)

                If [get(foundcount) <> 1 /* failed to find just one item */]

                  Show Message ["Couldn't find item"]

                else

                  Exit Script [Inventory:: Price]

                end if

                 

                In your layout, I'd experiment with both the OnExit and OnModify triggers for the first script. It depends on your menu format options as to which will work best.

                 

                Good luck.

                • 5. Re: FM10 - Perform lookup on unrelated database
                  donjuancarlos_1
                    

                  "That's how I would do it, but you asked about setting this up for an unrelated table so I'll describe that in a following post."

                   

                  Yeah, the way you explained it with line items is how my purchase order database is set up.  Your replies got me thinking though.  I suppose it would be possible (with one heck of a long script) to have Filemaker auto-create all of the 50-odd line items in the correct order when a user creates a new Quote record.  Then I could use the standard portal layout, etc.  And I wouldn't have a quote database with 150+ fields, either. Thoughts?

                   

                  That means I would also have to have a script that checks each line to decide which Value List it's going to display in the dropdown menu... I'm not sure how to do that yet...

                  • 6. Re: FM10 - Perform lookup on unrelated database
                    philmodjunk
                      
                    donjuancarlos wrote:

                    "I suppose it would be possible (with one heck of a long script) to have Filemaker auto-create all of the 50-odd line items in the correct order when a user creates a new Quote record.  Then I could use the standard portal layout, etc.  And I wouldn't have a quote database with 150+ fields, either. Thoughts?"

                     

                    Funny you should bring that up. One version of our DB is for customers bringing in used beverage containers for recycling. This is a high volumne operation where we have to move large numbers of customers through with minimal delays. Thus we "pre-load" our invoices with the 4 items that represent 90% of the items our customers bring in. The script uses a loop, so a version of it should work for you with out being any longer, it'll loop 150 times instead of 4 is all.

                     

                    Define a global number field: gCurrentRow in Invoices.

                    Define a number field: ItemSeq in Catalog.

                    Create a relationship Invoices::gCurrentRow--=--Catalog::ItemSeq. I call this particular Table Ocurrence for Catalog, CRVsbyNumber.

                    In Catalog I enter numbers 1 through 4 (150 in you case), to identify the items I want to appear on every invoice and specify the order in which they will appear.

                     

                    Whenever a user clicks a button to trigger a new invoice, the following script executes:

                     

                    Freeze Window

                    New Record/Request

                    Set Field [Invoice::gCurrentRow, 1]

                    Loop

                      If [CRVsbyNumber::ItemID <> ""] /* Skip missing numbers */

                         Go To Portal Row [Select, Last]

                         Set Field [LineItems::ItemID, CRVsbyNumber::ItemID]

                      End If

                      Set Field [Invoice::gCurrentRow, Invoice::gCurrentRow + 1]

                      Exit Loop If [gCurrentRow > 4 /* 150 in your case */]

                    End Loop

                     

                    See how it works? You must be on the invoice layout when the script executes and have only one portal, If you have more than one portal on your layout, you have to add a Go To field step to tell filemaker which portal you are referring to in "Go To Portal Row".

                     

                    "That means I would also have to have a script that checks each line to decide which Value List it's going to display in the dropdown menu... I'm not sure how to do that yet..."

                     

                    You can do that pretty easily. I mentioned an "ItemType" field in your Catalog table? You can set an ItemType field in LineItems to look up the item type the same you lookup a unit price. Create a relationship linking LineItems::ItemType--=--Catalog::ItemType and you can now refer to this relationship in the value list definition for your drop down to limit the list to just the items of that type.

                     

                    Note: Given that you have 150 items, you might want to try the list view layout based on Line Items that I mentioned in a previous post for easier, cleaner printing. The above script can be modified to work directly on this layout instead of a portal.

                     

                    • 7. Re: FM10 - Perform lookup on unrelated database
                      donjuancarlos_1
                        

                      I really appreciate all this help!

                       

                      So I played around with the CurrentRow thingie, and then ran into another issue: Some of the Line items of the Quote are calculated values based on values entered in previous lines.  For example, I have an Eavestrough line item that auto-calculates its value based upon the dimensions of the building that the Quoter enters. I don't see how I would create a custom calculation for a single line item in a portal.  Perhaps a hybrid of the two techniques maybe in order?

                      • 8. Re: FM10 - Perform lookup on unrelated database
                        philmodjunk
                          

                        So I played around with the CurrentRow thingie, and then ran into another issue: Some of the Line items of the Quote are calculated values based on values entered in previous lines.  For example, I have an Eavestrough line item that auto-calculates its value based upon the dimensions of the building that the Quoter enters. I don't see how I would create a custom calculation for a single line item in a portal.  Perhaps a hybrid of the two techniques maybe in order?


                        Check out the "Auto Enter by calculation" options for your unit price field in Line Items. It looks like you might be able to come up with an expression that will do this for you.

                         

                        Something vaguely like:

                         

                        IF(catalog:: Description = "Eavestrough", expression referring to Invoice::building dimensions, Catalog::unit Price)

                        • 9. Re: FM10 - Perform lookup on unrelated database
                          donjuancarlos_1
                             Thanks, Phil.  I've got the guts functioning.  I just now have to add all of the portal line customizations and calculations, which will take awhile, because the requirements keep getting added to.  :)