10 Replies Latest reply on Jan 8, 2015 4:37 AM by makerbert

    Second foreign key not entered

    makerbert

      Hi All, noob here. Did some rdbms before but not as a developer. Apart from that Filemaker is new to me, this being my first project... I assume I forget something basic but I can't find it.


      I’ve got three tables; products, orders (“Meetlijst” in our lingo) and orderdetails (“MeetlijstRegel” to us). Orderdetails has relations with the other two tables as pictured below

      threeTables.png

      What I want to achieve is to store the ID-fields of both PK_ProductID and PK_MeetlijstID in the middle table in the FK_ representations but I can’t get that to work.

       

      I have a layout connected on the rightmost table with a portal to the middle table, that relationship seems to work and the ID from the rightmost relation is filled in in the middle table. How can I get the ID from the leftmost table in the middle table?

       

      Relationships between tables is a straightforwarded PK_ProductID = FK_ProductID and PK_MeetlijstID = FK_MeetlijstID. The primariy keys are set up identically using GET (UUID)

       

      I'm fairly new to filemaker, can someone show me the error of my ways or point me in the right direction?

       

      Thanks in advance for your responses and Merry Christmas to you all!

        • 1. Re: Second foreign key not entered
          gcatnine

          Despite the fact that without translation the example is very difficult to understand, in the portal in the “Meetlijst Desktop” layout, instead of MeetlijstRegel::Boomsoort field use MeetlijstRegel::FK_ProductID.

          • 2. Re: Second foreign key not entered
            siplus

            I did what you asked for, but I would structure the whole thing in a completely different manner...

            • 3. Re: Second foreign key not entered
              erolst

              My Dutch / Flamish is a bit rusty, so I'm not sure what a MeetlijstRegel represents, but the basic idea behind a join table portal setup is this:

               

              You create the join table record via the portal from the context of one parent, so FileMaker automatically sets that foreign key (so that the new record is a related record); then you use some selection mechanism to fill in the other key (or keys; a join table isn't limited to only join two “parents”).

               

              Basic example: an order system where you create line items from the context of the order, then use a value list / selection portal / whatever to select the product / item / article (and set the quantity) of the line item.

               

              Your error is to expect that Filemaker knows what your join table is supposed to express. In the example above it's “obvious” you want a new line item, but for which product? That's something only you / the user can know.

               

              If, OTOH, what you really want is to create a lookup table that holds one join record for each Meetlijst / Product combination, you can / must script that.

              • 4. Re: Second foreign key not entered
                gcatnine

                That's possible, but you have to unset the "do not replace exiting value of field (if any)" in the definition of the field. Otherwise, you will not be able to change the product in that line (the PK_productID remain the same even if Boomsort change.

                • 5. Re: Second foreign key not entered
                  makerbert

                  Basic example: an order system where you create line items from the context of the order, then use a value list / selection portal / whatever to select the product / item / article (and set the quantity) of the line item.

                   

                  Thats exactly what I want!

                   

                  Your error is to expect that Filemaker knows what your join table is supposed to express. In the example above it's “obvious” you want a new line item, but for which product? That's something only you / the user can know.


                  I already suspected I'm doing something wrong, trouble is I'm not thinking in filemaker yet, just in sql and Flamish 


                  Now, how would I go about to make sure to insert the correctr PK_ProductID when adding a record to orderdetails (meetlijstregels)? I tried using a popup menu displaying values from 'fields Product::PK_ProductID while displaying Product::ProductCode and that seems to work, the correct records get written in the MeetlijstRegel table but the FK_ProductID stays empty ....


                  Thanks for your feedback

                  • 6. Re: Second foreign key not entered
                    makerbert

                    Siplus,

                     

                    Thank you for your example, especially the calculations in the table.

                     

                    The ExecuteSQL statement in the calculate field is very clarifiying. I can think in SQL so this wil come in handy.

                     

                    Somehow I suspect there must be a more 'filemaker way' of doing this.

                     

                    Well, Everybody thanks sofar for your replies, I'm off to to Christmas for now and hope to meet you all here again after the holidays.

                     

                    Seasons greetings!

                    • 7. Re: Second foreign key not entered
                      erolst

                      makerbert wrote:

                      Now, how would I go about to make sure to insert the correctr PK_ProductID when adding a record to orderdetails (meetlijstregels)? I tried using a popup menu displaying values from 'fields Product::PK_ProductID while displaying Product::ProductCode and that seems to work, the correct records get written in the MeetlijstRegel table but the FK_ProductID stays empty ....

                       

                      Put FK_ProductID into the portal, format it as a popup and use the value list described above.

                       

                      If the relationship allows record creation, you can (within the last [spare] row) select a product code from the popup FK, which will create a new record and insert the associated PK_ProductID (while what you are seeing in the popup is the product code).

                       

                      Now this new record has the FK_productID that you selected, while the FK_orderID is inserted according to the relationship definition.

                       

                      Make sure that you copy, NOT reference data like price, which must not be affected by changes in the parent tables.

                       

                      PS: This is a rather basic stuff; I'm not sure why ExecuteSQL() should be necessary here, as was previously suggested.

                      • 8. Re: Second foreign key not entered
                        siplus

                        Always start with what you would like to see from an end user's point of view and squeeze your brains around that, not forgetting at the same time what will happen when the datasets grow.

                         

                        In your case, that means a portal on clients, with a filter field, because you can have thousands of them; the same thing on products. With 1000s of products and - hopefully - clients, the last thing you want is to scroll down a popup menu to find the right one, and with clients it can be even nastier because you could end up having 10 John Smith, so you want some other fields to display, too, in order to help you make a unique choice.

                         

                        You want these 2 portals on the left of the order creation portal, which shows the line items you've inserted into the order so far; you want to simply click on an item (or a client) and this simple click must add it to the order (or set the order's client).

                         

                        You also want the client portal to have just a few lines, say 5, while the products portal should be bigger, say 15. Order portal could be say 25. All portals scrollable, the first 2 are filtered on the search key.

                         

                        That's what you might want, although your mileage may vary. Design it on paper, fiddle with paper and pencil till you're satisfied, then begin to think how to implement it, then code it - because your decisions might influence your entire data structure.

                        • 9. Re: Second foreign key not entered
                          wimdecorte

                          erolst wrote:

                           

                          Flamish

                           

                          Appreciate your effort to understand, but allow me to correct It's where I was born.

                           

                          Flemish - Flemish - Wikipedia, the free encyclopedia

                          • 10. Re: Second foreign key not entered
                            makerbert

                            All,

                             

                            Thanks for responding, I have learned from this guy:

                             

                            https://www.youtube.com/watch?v=VJqA03X33DQ

                             

                            that video provided me a workable solution

                             

                             

                            B