1 2 3 Previous Next 39 Replies Latest reply on Jun 19, 2014 10:02 AM by AmberDavis

    Linking an edit box to another edit box with an IF statement

    AmberDavis

      Title

      Linking an edit box to another edit box with an IF statement

      Post

           Hello.

           I have made Filemaker to where the customer information (Name, phone #, email, account #, customer type) is on my companies item order sheet and it pops up automatically when you pull their name from the drop down list. I am struggling with linking the box "Customer Type" to the price of the item they need. Customer type includes (Retail, Distributor and Local) and each "type" has different prices. When I select an item to put on the order sheet I want Filemaker to supply the price of the item in the correct category i.e. If I select a dozen doughnuts and the customer is retail then the price = $20. If I select a dozen doughnuts and the customer is local the price = $10 and FM will set the "price box" automatically. I'm thinking an IF statement is appropriate that IF customer type is retail then pull up the record of the object and display the retail price.

           (Everything in our inventory has been filed and has all three prices listed so it is easy to pull).

           Please help and thank you

      Screen_Shot_2014-06-16_at_9.28.11_AM.png

        • 1. Re: Linking an edit box to another edit box with an IF statement
          philmodjunk

               I would not use an If function, but I might use Case(). It depends on how you store those different prices in the table from which you look up these prices.

               Assuming that you used this typical data model:

               Customer-----<Invoices-----<InvoiceData (line items)>-------Products

               You might have one products record for each combination of customer type and product. So to use your dozen donuts example, you might have 3 records for a dozen donuts, one for each customer type. Or you might define 3 different price fields in a single record for your dozen donuts.

               Which  method do you plan to use?

               And does your set of tables/relationships match what I have outlined?

          • 2. Re: Linking an edit box to another edit box with an IF statement
            AmberDavis

                 We set it up so that there are 3 different price fields in a single record! 

                 And I'm not sure if the set of tables/relationships match your outline. Where would I find that?

            • 3. Re: Linking an edit box to another edit box with an IF statement
              philmodjunk

                   To see what your relationships are, see Manage | Database | Relationships. If you are not sure that what you have matches what I posted, either see the first post of this thread: Common Forum Relationship and Field Notations Explained or you can upload a screen shot of what you have.

                   If you have the relationships that I outlined, I'd set up a price field in Invoice Data (sometimes called "Line Items"), that uses the Case function in an auto-enter calculation to copy the current price over for the specified combination of customer type and product ID.

                   Case ( Customer::Customer type = "Retail" ; Products::RetailPrice ;
                              Customer::Customer type = "Distributor" ; Products::DistributorPrice ;
                              Customer::Customer type = "Local" ; Products::LocalPrice ;
                              "Error " // this last value will only be returned if Customer type does not equal any of the above types
                            )

                   Please note that if you create a new invoice, add some items to the portal to Invoice Data and then change the value of Customer::Customer Type to be a new value, the prices shown in the above InvoiceData::Price field will not update to show the new customer type. That will normally be a desirable feature here as you don't want current changes to affect the prices shown on past invoices when the customer type was different.

              • 4. Re: Linking an edit box to another edit box with an IF statement
                AmberDavis

                     Hopefully I have the relationships you are talking about because that Case function sounds exactly like what I would need. Tell me what you think. It looks like they are all connected the way you say, just not in the proper order. Your link was very helpful by the way

                • 5. Re: Linking an edit box to another edit box with an IF statement
                  philmodjunk

                       Those relationships are different. My best guess is that your layout is based on PurchaseOrders with fields from Customers included on it. And that "Transactions" is the table where you list each part ordered for a given purchase order.

                       If so, (but note that I am guessing here), then the Price field that I described as being set up with an auto-enter calculation would be defined in transactions, but you also need to make a new Tutorial: What are Table Occurrences? of Inventory and link it by Item ID to Transactions and the auto-enter calculation would refer to price fields in that new occurrence of Inventory.

                  • 6. Re: Linking an edit box to another edit box with an IF statement
                    AmberDavis

                         Yes you are spot on about Transactions. So my next step will be to set up a price field that you described in the above comment and i should also make a duplicate table of inventory that is a table occurrence and then link it to Item ID and then link Item ID to transactions?

                    • 7. Re: Linking an edit box to another edit box with an IF statement
                      philmodjunk

                           Yes. If "table occurrence" is a new term, please click the link provided as it will take you to a thread that describes table occurrences in detail.

                      • 8. Re: Linking an edit box to another edit box with an IF statement
                        AmberDavis

                             Ok. Thank you, I will give it a try! And yes I read your article and will follow it step by step it is very informative! Thank you.

                        • 9. Re: Linking an edit box to another edit box with an IF statement
                          AmberDavis

                               Hello Again I tried what you said and the box would not display a price. I set it up as you said and even tried extra. I named the field "Prices" for the case function you gave me. And I linked it with what you told me. I also tried to go into the script and see if i could do something I feel like I am close, I'll attach the script with a blue highlight on the if statement I made...It makes sense but it doesn't work I feel like I'm one step away. I'll also attach the relationship chart that I made. Thank you for your help! 

                          • 10. Re: Linking an edit box to another edit box with an IF statement
                            AmberDavis

                                 This is the new relationship web you told me to create

                            • 11. Re: Linking an edit box to another edit box with an IF statement
                              philmodjunk

                                   What I described would not be set up with a script. It would use an auto-enter calculation specified in Field Options.

                                   Once you have the relationships in place, defining a number field (price) field in Transactions with

                                   Case ( Customer::Customer type = "Retail" ; Products::RetailPrice ;
                                              Customer::Customer type = "Distributor" ; Products::DistributorPrice ;
                                              Customer::Customer type = "Local" ; Products::LocalPrice ;
                                              "Error " // this last value will only be returned if Customer type does not equal any of the above types
                                            )

                                   as the auto-enter calculation, you are done, no other scripting required. But the only match fields linking records between Transactions and Inventory 2 should be the Item ID field. Prices makes no sense as a match field here.

                                   And your screen shot shows that the storage options for Prices are incorrect. And I see no need for the additional occurrence of Customers linked by Customer Type as this is not needed for what I have descirbed (and won't work with the match fields that you have selected anyway.)

                                   And you might want to look up the GetField function as this is not how you would use that function. wink

                              • 12. Re: Linking an edit box to another edit box with an IF statement
                                AmberDavis

                                     I wish this code was simple like C++! But anywho, I fixed everything you said but the "prices" box still won't show the price. I'm beginning to understand relationships better. If you have an article on relationships I would love to read it as well. I have attached a picture everything and auto entered the "prices" field with a calculated value. I think the problem is the FM page has the field "prices" in a text box but it can't connect the "Prices" calculated value of Customer Type = "Retail" to the Customer Type box at the top of the page. I screen shotted it (The black background). Should we connect Customer Type to Prices in the relationship web?

                                      

                                • 13. Re: Linking an edit box to another edit box with an IF statement
                                  philmodjunk

                                       In my opinion, FileMaker syntax is no worse than C++ and often much less cryptic, but for you, it's still unfamiliar territory and designing relational database systems--while it has a lot in common with programming in a language like C++, it also has many differences.

                                       Let's check some basic assumptions that I am making to see what might not actually be the case in your file:

                                       Assumptions that I made:

                                         
                                  1.           The layout is based on PurchaseOrders.
                                  2.      
                                  3.           There is a portal to Transactions on this layout.
                                  4.      
                                  5.           You then select an item from the Inventory table by entering (or by selecting from a value list)
                                  6.      
                                  7.           When you double click the Price field in Transactions, to open field options, the Case function is listed as the auto-entered calculation and the "do not replace existing value" check box has been cleared.
                                       


                                            If you have an article on relationships I would love to read it as well.

                                       Hmmm, well the link on Tutorial: What are Table Occurrences? that I already posted IS about database relationships.

                                  • 14. Re: Linking an edit box to another edit box with an IF statement
                                    AmberDavis

                                         1. Yes

                                         2. Yes

                                         3. Yes, you select an item from the drop down list "ItemID" and the items are pulled from our inventory section

                                         4. Yes

                                    1 2 3 Previous Next