1 2 3 Previous Next 44 Replies Latest reply on Mar 17, 2015 12:22 PM by philmodjunk

    Linking the same table with two occurences

    AndrewJudd

      Title

      Linking the same table with two occurences

      Post

       

      hi there, i'm trying to get my consignments table to link to my clients table in the same way my invoice table does.

      ie. you can cross reference the consignment details in the client section, like you can with invoieces. I've attempted to follow the same formula (see attached).

      The issue I'm having (I think) is that as the graph limits you to no more than one relational path between. The script that I use when adding a client from the clients table to a new consignment record (which I'll include below too) does pull the correct client id from the client table but it puts it into clients 2 so the relationship isn't complete.

      In invoices I have it so you can click the name of the client and I have a button which goes to the related record. When you click on the name of the client in consignments (with the same button) nothing happens.

      Very difficult to explain but perhaps somone can help from looking at the attached.. thank you

       

       

       

       

       

       

      Screen_Shot_2015-03-10_at_08.21.15.png

        • 1. Re: Linking the same table with two occurences
          AndrewJudd
          /files/b6c7332e5c/Screen_Shot_2015-03-10_at_08.26.20.png 545x410
          • 2. Re: Linking the same table with two occurences
            philmodjunk

            The script that I use when adding a client from the clients table to a new consignment record (which I'll include below too) does pull the correct client id from the client table but it puts it into clients 2 so the relationship isn't complete.

            That makes no sense, for one thing, clients and clients 2 should refer to the same table and so "putting it into clients 2" should also be putting it into clients as they are one and the same table. Secondly, your script shown doesn't modify any data in clients nor clients 2, it modifies a field in consignment forms which, if the value of script parameter is correct, is exactly what it should do.

            • 3. Re: Linking the same table with two occurences
              AndrewJudd

              Thanks Phil. That was what I understood how the tables work but it just seems odd that I can't get the consignment form table to work like the invoice table does. In terms of the relationships.

              What I have done is added "client id 2" to my client layout, alongside the original "client id". it's notable that both fields don't populate with the same number. Screenshot attached.

              • 4. Re: Linking the same table with two occurences
                AndrewJudd

                This is my script parameter

                • 5. Re: Linking the same table with two occurences
                  AndrewJudd

                  maybe I need to add something like this?

                  • 6. Re: Linking the same table with two occurences
                    philmodjunk

                    Putting fields from Clients and Clients 2 side by side on your clients layout doesn't show you or I anything significant. Like any other case where you add a field to a layout from a different table occurrence, the relationship specified between the two occurrences and the value of your match field(s) in the layout's current record will control what data, if any, appears in the field. You won't necessarily get the same ID's or even any data at all appearing in the field from the related table--even when it's an occurrence of the very same table.

                    You'll need to look at what values is actually being passed to your script in the script parameter.

                    I suggest that you either run the script FileMaker Advanced's Script Debugger while using the data viewer to watch the value of your fields and using Get ( ScriptParameter) in a watch expression to see what value is actually passed as your script parameter, or add a show custom dialog with Get ( ScriptParameter) in it to your script so that you can see if the ID is actually getting passed to the script. (context issues might prevent this even though your script parameter seems to refer to the correct value.)

                    A closer look at the design of your layout might also reveal issues that are keeping this from working.

                    • 7. Re: Linking the same table with two occurences
                      mchancevet@gmail.com

                      I may have misunderstood the problem here but could you assign clients to consignments using a portal on the/a consignment layout? You could populate the client ID (I assume this is the relationship match field) using a drop down list showing client names but actually inserting ID's into the field. 

                      I'm a bit confused about your relationships 'Invoice::ID X All Client Popover::ID' and 'Consignment::ID X All Client Popover::ID'

                      Do you want the invoice or consignment (let call then 'documents' collectively) to match to a client ID with the same ID as the document ID?

                      It seems to me it might work better if the relationship went Invoice::ID_customer X All Client Popover::ID

                      Regards,

                      Morgan

                      • 8. Re: Linking the same table with two occurences
                        philmodjunk

                        Andrew Judd is not using a drop down list. While that works, it's not very user friendly once you get a fairly long list of values to choose from your list.

                        Instead, he's attempting to use a value selection portal placed inside a popover. You can find an example of this inside the starter solution he mentions. You can also find examples of this method in "Adventures in FileMaking #2 - enhanced value selection" where this method is documented in a great more detail than found in the starter solution where there is no real documentation to speak of at all.

                        • 9. Re: Linking the same table with two occurences
                          Timothy Bentley

                          In invoices I have it so you can click the name of the client and I have a button which goes to the related record. When you click on the name of the client in consignments (with the same button) nothing happens.

                          I wonder if the button for consignments may get the related record from "CLIENTS" when it should be from "CLIENTS 2".

                          • 10. Re: Linking the same table with two occurences
                            philmodjunk

                            I think that's a very likely possibility here. When GTRR is executed and there are no related records to "go to". Nothing happens except that an error code is returned that you can test for with Get ( LastError ). Referring to the wrong table occurrence as the "table" parameter could easily produce this result.

                            • 11. Re: Linking the same table with two occurences
                              AndrewJudd

                              Thanks all. Indeed the related field did need directing to clients 2. That is working now. However for some reason the clients section still doesn't "see" the consignment key (I have a field in clients which is this - List (CONSIGNMENT FORMS::_id__kp).

                              A more important thing to sort out is this.......

                              I need to be able to add artworks to multiple consignments. Ie. the same inventory product may be added to multiple consignments over time. (ie. an artwork could be shown in several exhibitions and need to go on several bits of paperwork).

                              The issue I have is that I am using my invoice section as a basis for the consignment area. Obviously you only sell and artwork once, to one person. So that is fine.. But I need a different idea for the consignments table.

                              How can you set things up so when you add an artwork to a new consignment (an artwork that is already assigned to a previous consignment) it generates a new match key but keeps the old one? 

                              And is that even the way this should be done? So you have a list of matched keys? And therefore you easily have a clear history of consignments if needed.

                              Thank you

                               

                               

                               

                               

                               

                              • 12. Re: Linking the same table with two occurences
                                AndrewJudd
                                /files/d6b2ccc173/Screen_Shot_2015-03-12_at_12.35.30.png 1278x429
                                • 13. Re: Linking the same table with two occurences
                                  AndrewJudd

                                  (someone very kindly helped me re-organise my tables)

                                  • 14. Re: Linking the same table with two occurences
                                    philmodjunk

                                    I have a field in clients which is this - List (CONSIGNMENT FORMS::_id__kp).

                                    You need to select Clients 2, not clients from the "context" drop down found at the top of the specify fields dialog box.

                                    For your main question, you are describing a many to many relationship. An artwork can be linked to many consignments, a consignment can link many artworks. And this can be exactly like an invoices to invoice details (also called "lineitems") type relationship as the same product is often listed on more than one invoice (when it's not a one of a kind artwork like you have here.)

                                    So you need to keep the current arrangement, but change your match fields:

                                    Clients 2-------<Consignment Forms>-----Artwork

                                    Clients 2::__pkClientID = Consignment Forms::_fkClientID
                                    Artwork::__pkArtworkID = Consignmnet Forms::_fkArtworkID

                                    I've renamed the match fields used to make it clear where they are defined as auto-entered serial numbers (__pk) or simple number fields (_fk) and whether they uniquely identify the Client record or the artwork.

                                    Note that to list the same art work for two different consignments simply requires creating a consignment record that is then linked to a particular client and the artwork.

                                    1 2 3 Previous Next