14 Replies Latest reply on May 9, 2012 10:27 AM by MarkBløndal

    populating fields based on primary key without creating a new record.

    MarkBløndal

      Title

      populating fields based on primary key without creating a new record.

      Post

      Hello

       

      I am trying to populate 4 fields based on the input from one field

      (From one table:Personnel, into a form in another table:Borrowed . All 5 fields are from the personnel table.)

      My setup is quite straight forward

      Field 1: CPR Number (Primary Key) Personnel::CPR

      Field 2-5: related information based on Field 1(Personnel::name, Personnel::email, phone, and group).Personnel::phone, and Personnel::group).

      I tried simply inserting a field from table:Personnel, but when I typed in CPR it would search and populate Field2-5 correctly BUT in my Personnel table it would then create a new record with the CPR value - And no other values.

      Instead of (as expected) simply using the value from an already existing record with that CPR number.

      I apparently can not search Personnel unles I make the relation between them able to create in both tables.

       

      The whole point is to make Borrowed a form for items to lend to personnel, without ever changing the Personnel table.

      Borrowed is a mix of Equipment and Personnel neither of which is supposed to be changeable.

       

      What would be the ideal way to perform this simple task ? 

        • 1. Re: populating fields based on primary key without creating a new record.
          GuyStevens

          Here's how I understand what you want to do:

          You have a table with personell, a table with equipment and you want to create a table of "Borrowed Equipmment"

          In this table you would have:
          - An ID for every "borrow"
          - A Personell Id Foreign Key because every item is borrowed to one member of personell.
          - An equipment Id Foreign key but only if there can only be one item borrowed. If you want to borow multiple items to a person at the same time then you need a sort of line items table. Like you have in an invoice.

          A line items table would have:
          - It's own Id
          - A "Borrowed" Id foreign key
          - An Equipment Id foreign key.
          - Possibly an "amount" if you can borrow multiple items of one piece of equipment" (like 5 chairs)

          Maybe to your "Borrowed" table you could add fields like:
          - Date
          - Borrowed untill
          - Authorized by.....

          Let me know if you need any more help or if I completely misunderstood your question.

          • 2. Re: populating fields based on primary key without creating a new record.
            MarkBløndal

            Thank you for your reply.

             

            I have multiple tables and I need them all to cooperate in a single layout:

            http://dl.dropbox.com/u/2197128/layout.png

             

            The field beneath "Udlånt til" is for the CPR number, when I input the number ID there it should populate the fields beneath it.

            And it works fine when it is made of fields directly from the Personnel record (i.e Personnel::Name), then I only need to input CPR and the rest is automatic.

            But if I use the direct field link, FMP for some reason creates a new line in my Personnel record only containing the CPR number.

            the DB overview:

            http://dl.dropbox.com/u/2197128/db%20Overview.pdf

             

            I think I basically need a lookup function for several fields based upon a single input.

            And this has to be done without creating new records in the table I am searching.

             

            Would love to be able to show a bit more but the data itself is sensitive.

            • 3. Re: populating fields based on primary key without creating a new record.
              MarkBløndal

              I seem to have solved it myself.

               

              It was necessary for me to create a field in Borrowed, called "CPR Borrowed".

              And through that field I could search the data from "Personnel::CPR" without creating any new data.

               

              I wasn't aware that the direct fields behaves differently than I assumed.

               

              I thank you for your help, and am a little embarresed it was something so simple.

              Just have to get used to FMP and the way it structures everything.

              • 4. Re: populating fields based on primary key without creating a new record.
                GuyStevens

                What you need in that CPR field in the "Udlan" table is a dropdown list that get's it's value from the CPR field in the personnell table.

                If you want you can set this dropdown list to also show the personnell name in the second row.

                You can even only show the personnell name from the second row while setting the CPR value (in the first row) of your dropdown list.

                So basically you are setting the CPR number from your personnell record in your CPR field of your "Udl°an" table.

                This CPR number is what's called a Foreign key. And let's the "Udlan" table know which personnell record needs to be linked to this "Udlan" record.

                Then you can get all of the other fields such as Name, Klasse, Telefon, Email, ... from your Personnell table and the correct fields for the selected personnell will appear.

                Just don't set the CPR field from the Personnell table in your layout that's based on the Udlan table.

                • 5. Re: populating fields based on primary key without creating a new record.
                  GuyStevens

                  The idea is simple.

                  You create a table called Personnell.
                  Every Personnell had an Id. This is a number field set to Auto Enter a "Serial Value". That way your personnell always get's a unique number within that table.

                  For your Equipment you do the same. In fact every table has it's own Id. Always the same. A Number field set to Auto Enter "Serial Value".

                  Then you can start linking.

                  If you want to link a personnell record to your Udlan you need the following fields:

                  In your Udlan Table:
                  It's own Unique Auto Enter Serial Number Id
                  A number Field to store the Unique Id of the Personnell Table (Called Foreign Key)
                  A number Field to store the Unique Id of the Equipment Table (Called Foreign Key)

                  Then you make a relationship from your Personnell table's ID to the Udlan Table's PersonnellId_Foreign_Key (IdFk)
                  Then you make a relationship from your Equipment table's ID to the Udlan Table's EquipmentId_Foreign_Key (IdFk)

                  Then you set the ID's with dropdown boxes and you are good to go.

                  That's the basic idea.

                  • 6. Re: populating fields based on primary key without creating a new record.
                    GuyStevens

                    I'm not sure if I'm seing Unique Id's in all of your tables, so you might want to look into that.

                    You can create a field and then fill it with a script step of "Replace Contents with Serial Number"

                    • 7. Re: populating fields based on primary key without creating a new record.
                      MarkBløndal

                      Every ID I am using is unique:

                      CPR is unique(social security ID)

                      OEG Label ID is unique it's etched onto the units

                      LabelID is also etched on

                      OEGnr is an automated sequential number so also unique.

                       

                      I ended up creating a field called "CPR Udlan" and in that field i asked for information from "AlleElever(personell)::CPR"

                      That made it possible to look up the value without it creating a new record in personell.

                      Been double/quadriple testing it the last 15min, and it behaves as I expected/hoped.

                      I thought I was able to search the personell by directly linking it to a field, but as it turns out you need a field in Udlan in order to Search and not SearchAndCreate.

                       

                      If you see any flaws in my setup, I would appreciate it since I would like a truly functionel setup.

                      • 8. Re: populating fields based on primary key without creating a new record.
                        GuyStevens

                        Does your "Udlan" table have a Unique Id?

                        It's a little hard for me to find the ID fields because of the language.

                        And I always create an ID field as the first field in EVERY table I make.

                        So my tables would always have an ID field on top.

                        It's like a standard that a lot of people use.

                         

                        Tell me a little what the Power table is and the Skadesanmeldelser table.

                        You have two tables that use the LabelID. Could you explain those?

                        I assume that the Macbook Oversigt is a list of Macbook Computers. I would still give them a Unique Id serial number from within Filemaker.
                        That way if you ever give in an incorrect label Id and you change it later on there is no problem because the relationships will be make on the Unique Filemaker created serial number.

                        If you make relationships based on the Label Id and you correct an incorrect Id then your relationship is broken.

                        The Idea is that the Id's that you let filemaker make are never shown to the end user and are not modifiable.

                        • 9. Re: populating fields based on primary key without creating a new record.
                          MarkBløndal

                          The Label ID's are a list of ID's based on a system where we put acid on the unit and etch the ID onto the unit, so there are no way for it to dissapear and no way for it to have a double.

                          It's a use and throw away system so you can't burn/etch/acid the same number twice.

                          Skadesanmeldelser, is another table of broken items which is relevant when lending a replacement out.

                          But both the lending and the faulty unit is typed into the same window due to the nature of the relation, which is also why they use OEGNr since it's unique and there isn't really a need for a seperate ID since it's not going to change and there can only be one.

                           

                          Basically, I've chosen a unique ID that is visible when looking at the DB but also when handling the actual unit.

                          And given that it is a unique ID I didn't really see an issue as to why not use a number everybody in my department can relate to.

                           

                          But if I am completely off, and you can see a scenario where my use of ID's would cause a conflict I will of course change it to something auto generated / FMP created.

                          But I can assure you the Label ID's can not excist twice.

                           

                          Thank you for being so thorough in you help.

                          • 10. Re: populating fields based on primary key without creating a new record.
                            GuyStevens

                            Here is a scenario:
                            You have a new item and it needs to be rented out immediatly.
                            You create a number with your label system and you put it on the device with acid. Let's say the number is 123456.
                            Then you enter that item into the database and you accidentally type 123465.
                            Then you use a dropdown in your "Udlan" table and select your item with OEG 123465. Because you are in a hurry you don't see the mistake.
                            Afterwards the item is returned and you search for the number that's on the unit. It's 123456. You can't find it and discover your mistake.
                            You go into the table of the units and you change the number to 123456.
                            But now in your Udlan table there is a record with a reference to item 123465 thas was loned out. But that item no longer exists in the database.
                            Now you have a problem.
                            Which item was it?

                            You can still use the OEG numbers. I would just not use them as the basis for relationships.
                            But you can choose, it's your database.
                            I just like to play safe. Manual entry of ID numbers can lead to mistakes.

                            Also, you create the Label Id using another type of system. You could do that using filemaker.
                            You could let filemaker generate a unique serial number and then combine that with whatever other form of code you would like to add.
                            But then you would have to first create a new record in filemaker to be able to see what the new "Label ID" for a new piece of equipment is.
                            So that depends on your workflow.

                            About the OEG numbers: you could add them as a field in your table of items and then people could still find a certain item by searching for it's OEG number.

                            Also, you didn't anwser my question about whether the Udlan table has a Unique Id?

                            Here is another scenario:

                            You create a layout for printing "Udlan" documents. On this document is all the information that's in one "Udlan" record.
                            You want to print one "Udlan" How do you do that?

                            I would make a script that goes to the layout of the "Udlan" print document and search for the Unique ID of that particular "Udlan" record and print that one found record.
                            But if your "Udlan" records don't have a unique Id, then what do you look for?
                            Every person can have multiple records in the "Udlan" table.
                            Every piece of equipment can be borrowed multiple times.

                            If you want to borrow multiple items you need a sort of "Line Items" table and that needs to be linked to the "Udlan" Id.

                            You use two different EOEG numbers, one for your Macbook Items and one for the broken items. You might want to be carefull in how you name these records in the Udlan table because you might make mistakes.

                            • 11. Re: populating fields based on primary key without creating a new record.
                              GuyStevens

                              This is what my relationship graph would look like:

                              Relationships

                              I don't know enough about your Label to see where I would generate it.

                              • 12. Re: populating fields based on primary key without creating a new record.
                                MarkBløndal

                                We scan the ID using barcode scanner to avoid that exact error but I do see you point.

                                The form/layout is made so that both lending out and reporting broken items is done using the same OEG Nr. since 90% of the time we lend stuff out because of broken items.
                                It is then joined in the same form because it is connected cases.

                                i.e when a person comes to my department they say: "hey this is broken, can you fix it and can I borrow a replacement unit in the meanwhile".
                                We then simply create a single new record but can record both the item we lend out and the case for the fix.

                                Then when we search for either the person or the OEG Nr. we get "both" cases, in a single layout and we are sure they correspond/are connected.
                                The same person can lend and break stuff multiple times, so it's easier to connect the two in order to secure it is the correct broken item that goes with the lent item. 

                                But you gave me a good idea for the Label ID, I can make the field an automatic sequence and tell it to start from the next number in our etching kit.
                                That way it's both auto generated and matches the ID from the etch/acid. 

                                About the print, I have secured the layout is printable and we actually dont print it.
                                I've created a script that gathers the information and sends it as an email instead - but if a print is necessary "cmd+p" does indeed print only the current record.

                                And I have also made a layout that gathers the records in a list view, enabling both quick overview and sorting based on current cases.
                                It's important when lending out stuff, so of course I am able to view "missing" items.

                                The whole shift to FMP is in an attempt to get as close to paperless as possible.
                                signature is handled using an iPad, and the reciept is send by email, all in order to avoid using paper. 

                                • 13. Re: populating fields based on primary key without creating a new record.
                                  GuyStevens

                                  Sounds like you have everything under control :)

                                  I guess if you ever need an "Udlan" serial number you could just create a field and automatically fill it with a serial number.

                                  The reason I talked about the printing is that usually you make one layout that works best on screen and another layout that is optimized for printing.

                                  Because of the paper size and the screensize these layouts sizes might be different.

                                  And also certain fonts look better on screen and others better in print.

                                  It's in this kind of case that you would have to change from layout but still arrive on the same record. The only way to find that specific record is with it's Id.

                                  For your etching kit, I don't know what kind of numbers you use now but you are not limited to a boring serial value (1,2,3...) Yo can always use a calculation field to add more data or other numbers to that serial number.

                                  • 14. Re: populating fields based on primary key without creating a new record.
                                    MarkBløndal

                                    Yes I found out about different layouts for printing and stuff, a little late.
                                    I had already made my layout compatible with printing and don't really feel like creating a completely new layout.

                                    It's actually made so that it works both for desktop, iPad and when printing (from both desktop and iPad).

                                    Although some of the layout when editing looks nothing like it does in "use" and "preview", which does make it difficult to anticipate behaviour. 

                                    But thank you for all your feedback, it really helped clear out a few worries.