1 2 Previous Next 18 Replies Latest reply on Jun 16, 2011 7:14 AM by yuichim

    Question about separation model solution

    yuichim

      Title

      Question about separation model solution

      Post

      I am in the process of creating a customer relations solution (including order form and invoicing) using separation model.
      The only thing is that I am a bit lost on how to proceed....

      Right now, I am creating the order form part, in which the layout is using the Orders table, in which it has a relations of:

      Accounts Table (accountid -> kf_accountid) Order Table (orderid -> kf_orderid) OrderItems Table (orderitmesid -> kf_porderitemsid) Products Table

      Order table contains a portal displaying data from orderItems in which are line items displaying qty, product descriptions, price, etc.

      The challenge I am facing now is that I can't display any pricing information (which is also a relationship from orderitems and products) in the portal without creating some kind of relationship graph in the data file.... which I am trying to prevent. If possible, I want to put relationship only in the interface file...

      What would be the best solution for this....? 

      Any help much appreciated..

        • 1. Re: Question about separation model solution
          philmodjunk

          Adding certain basic relationships in your Data file don't seem like such a terrible thing to do here.

          If you aren't going to use a relationship, you'd have to use a script trigger to perform a find on the Products table and then use a variable to copy the price from the record found in products to the current OrderItems record.

          It also looks like you have one relationship backwards but that may just be a naming convention error.

          Seems like you should have OrderItems::_kf_ProductID = Products::__pk_ProductID instead of: OrderItems::orderitmesid -> Products::kf_porderitemsid. However you name the fields, the serial number should be auto-entered in Products, not in OrderItems.

          The script: (using your field names though they appear to be inccorrect here.)

          Freeze Window
          Set Variable [$ProductID ; value: OrderItems::OrderItemsID ]
          Go To layout [Products]
          Enter Find Mode[]
          Set Field [Products::kf_porderitemsID ; $ProductID ]
          Perform Find []
          Set Variable [$Price ; Products::Price]
          Go To Layout [original layout]
          Set Field [OrderItems::Price ; $Price]

          • 2. Re: Question about separation model solution
            yuichim

            Hi Phil,

            Thank you very much for your response, as always!! 
            What you mentioned did work!
            However, the challenge is when you have multiple products in the portal row, how would FM know which orderitemsid to set into the variable?

            • 3. Re: Question about separation model solution
              yuichim

              I've done couple of test but it seems like when the trigger happens, it is getting the first portal row's value.
              Would that make any sense?

              • 4. Re: Question about separation model solution
                LaRetta_1

                As I have explained twice before, you can create your table occurrence groups (TOGs) in the Interface.  The ONLY time you need to create a relationship in the actual data file is if you need to create a calculation in in one of the tables which references one of its related tables.

                "The challenge I am facing now is that I can't display any pricing information (which is also a relationship from orderitems and products) in the portal without creating some kind of relationship graph in the data file.... which I am trying to prevent. If possible, I want to put relationship only in the interface file..."

                Try what I have suggested ... create Orders, LineItems, Products in your UI and join them properly.  Then create a portal from THOSE table occurrences.  You can pull information from anywhere when joined in the UI. All you need is to add a file reference to your Data file.  I have worked in many separation models and have only a few joins in the data file ... they are only rarely needed.  :^)

                • 5. Re: Question about separation model solution
                  yuichim

                  Hi LaRetta,

                  Thank you very much for your post!  In the interface file, I do have TOGs created and it seems to work great! 
                  I wasn't so sure on how to grab the prices but setting variables via permorming find and settinf fields did work the trick.

                  However, the way I have is that I have a portal showing line items with product dropdown to be able to change the product, if you need to...  and there is a script trigger to update the price on the portal row by changing the layout and performing the find/ and set field..
                  The challenge is that when I have multiple products in the portal and even though I try to change the products on it, it seems to only grab the first row's data... (in which I have a set variable to grab the orderitemsid....)  and that's how I found out..

                  • 6. Re: Question about separation model solution
                    philmodjunk

                    For dynamic links where you just need to display data from a related table, a relationship in the interface file suffices. If you want to use a looked up value setting to copy the data, this requires a relationship in the data file or a scripted approach.

                    The script I posted will pull values from the current portal row. If you use a script trigger set on the field where you enter/select the product ID inside the portal, then the row where you selected/entered the ID number should still be the current portal row and the script should work just fine. For some reason, perhaps due to a commit record kicking in somewheres, the portal row where you selected/edited the Product ID is losing the focus and thus the script, by default refers to the first portal row.

                    If you have FileMaker advanced, try triggering this script with the debugger enabled so that you can see what is happening here step by step and whether another triggered script might be interfering.

                    • 7. Re: Question about separation model solution
                      yuichim

                      Phil,

                      I have advanced and I did the test with script debugger on.
                      It looks like it is getting the data from the first row of portal.   Why do you think it is happening?

                      • 8. Re: Question about separation model solution
                        philmodjunk

                        I'd have to see the file. I use this technique with other portal initiated scripts (Like a delete portal row script) and it works for me. Can't tell from here why the portal row you are currently editing when the script is performed is losing the focus.

                        • 9. Re: Question about separation model solution
                          yuichim

                          Thanks Phil,
                          I know what you are saying.... I've done something similar and was able to set the variables like that but for some reason, this one is not happening.

                          Would you take a look at the file?

                          • 10. Re: Question about separation model solution
                            philmodjunk

                            You can upload a clone of the file to a share site and post the download link here.

                            • 11. Re: Question about separation model solution
                              yuichim

                              Thanks Phil,

                              Here is the download link:

                              https://docs.google.com/leaf?id=0B4awAADUJMC9NjY0NDBjMjYtYWViZi00Y2I0LWJlNjAtNTI1ZGY2ZDQ4NzVk&hl=en_US

                              You can login using admin and blank for the password.

                              I really appreciate for looking at the file.. :)

                              • 12. Re: Question about separation model solution
                                philmodjunk

                                Turned out to be an easy fix.

                                Remove the second perform find from your script. It's not needed and it's what removes the focus from the current portal row.

                                • 13. Re: Question about separation model solution
                                  yuichim

                                  Phil,  I don't know how much to appreciate.
                                  I have a question about removing the second "find".
                                  Don't I need that to update the prices on line items?

                                  Also, I forgot to mention but the layout is: New Order layout.

                                  :)

                                  • 14. Re: Question about separation model solution
                                    philmodjunk

                                    Not as your script is written. If you went to a layout based on the order items and updated it there, you'd need the find to isolate the correct order items record. But since you are just returning to the original layout, the current record, found set, sort order and portal row focus remains just like you left it.

                                    When you have a script that changes layouts, manipulates records and then returns to the original layout, the original layout will still have everything just like you left it unless the layout you changed to refers to the same table occurrence in layout setup | Show records from as the original layout.

                                    1 2 Previous Next