1 2 Previous Next 20 Replies Latest reply on Jun 9, 2012 8:30 AM by LaRetta

    creating relationships

    jacjs

      I would like to create a simple table that allows me to select 1 item in a field using a dropdown box and which will then automatically bring up the related value in the next field, eg. select procedure, then item number for that procedure is entered automatically in the field

       

      How do I do this please??

       

      I have only basic filemaker knowledge - please use non technical terms.

        • 1. Re: creating relationships
          Mike_Mitchell

          Hello, jacjs.

           

          This is a fairly common need. So you're not alone.   

           

          Commonly, folks will deal with this using related value lists and a couple of global fields. You allow the user to select in the first global field from all available values. Then, the value list on the second field is based on a relationship between that first global field and the related table. You set the value list to show only related values, and voila.

           

          However, it sounds as though your need is a little different. If there is only one related item for each selection in the first value list, you can set that second field to auto-enter the value, either by lookup or by auto-enter calculation. However, if more than one related item is available, you'll get the first related item, which may not be what you want.

           

          Maybe you can explain a bit more exactly what your need is and we can dig out precisely how to implement.

           

          Mike

          • 2. Re: creating relationships
            erolst

            Hi hacjs,

             

            without knowing the workflow or intended purpose of your solution it's difficult to give suggestions (it might even prove counter-productive!). Still, based on your description, here's a simple database which hopefully gives you some pointers on how to implement your idea; it also tries to explain what's going on behind the scenes in not-too-technical terms (though they can't be avoided completely). Let us know if you need further assistance.

            • 3. Re: creating relationships
              jacjs

              Hi Mike,

               

               

               

              With my table, for each selection in the first value list, there will be 2 related items. Please see table below:

               

               

               

               

              Item

               

              Item number

               

              Cost

               

               

              Consultation – comprehensive

               

              85011

               

              55

               

               

              Consultation – periodic

               

              85012

               

              45

               

               

              Consultation – post op

               

              -

               

              0

               

               

              Consultation – post op extra

               

              86986

               

              45

               

               

              Extraction tooth – simple elevation

               

              85311

               

              120 per tooth

               

               

              Extraction tooth – sectional

               

              85314

               

              150 per tooth

               

               

               

              I have a field for each row header above and when I select the “item” field, i’d like the “item number” and “cost” field to be automatically entered.

               

               

               

              Thanks,

               

              Jaclyn

              • 4. Re: creating relationships
                erolst

                Jaclyn, did you notice my example database?

                • 5. Re: creating relationships
                  Mike_Mitchell

                  Jaclyn -

                   

                  What you have is a single related record with two fields. It's fairly simple to read in; what you will do is copy the contents of each field into its target when the proper parent key is selected.

                   

                  Take a look at erolst's example file. It does what you need.

                   

                  Mike

                  • 6. Re: creating relationships
                    jacjs

                    Yes, had a look at the example database but unfortunately, I can’t seem to decipher it.

                     

                     

                     

                    I was wondering if it would be possible to list the steps out – is it possible to import an excel spreadsheet table into my filemaker file and set up the relationships that way?

                     

                     

                     

                    So far, I’ve created the table and created the fields. I’ve tried to create relationships between the fields using the “relationships” tab but beyond that, I’m not sure how to proceed.

                     

                     

                     

                    Thanks,

                     

                    Jaclyn

                    • 7. Re: creating relationships
                      Mike_Mitchell

                      Jaclyn -

                       

                      Your existing table isn't exactly what we would call "normalized" (that's database talk for "reduced to hold the data in a minimized way"). However, I think we can still work around it, since you have a unique (presumably) item number associated with each record. Here's what you do.

                       

                      Create a global field (you can call it Item Number Global, or gItemNumber, or whatever). In the Relationships graph, you should have a single table occurrence (TO) for the table where these records live. Take your mouse and grab the Item Number Global / gItemNumber field. Drag it off of the TO, then back onto the TO and release it on the Item Number field. FileMaker will automatically create a new TO for the same table, with the relationship being based on the Item Number field. (We call this a "self joining" relationship.)

                       

                      Once you've done this, you can look from the main table back into itself. In other words, whenever I sit on a single record and look across at the related table, I'll see whatever record matches the value in gItemNumber. What this does for me is allows me to copy the values from the current table onto the current record by inserting a value in the global field.

                       

                      Next, let's create a value list. In the File menu, choose Manage / Value Lists. Create a new value list based on the values in the table, and use the Item Number field to do it. Have it use all records to build the list, and include a second field so you'll know what they point to (Item is probably a good choice). Save the value list and exit the dialog.

                       

                      Now, it's time to set up the automatic entry. Presumably, you have some fields into which you want to copy the Item and Cost values when the user selects a procedure. Go into the Manage Database dialog and choose the field into which you want to copy the Item value. In the Options dialog, choose Lookup. Select the field in the related table occurrence (the self-join) for Item. Save that change. Repeat for the field into which you want to copy the Cost value, except this time, choose the Cost field in the related table occurrence.

                       

                      Save your changes by clicking OK in the Manage Database dialog. Then, on your layout in Layout Mode, attach your value list to the global field. Whenever you select an option in the value list, the Item and Cost will be automatically copied into the destination fields.

                       

                      Clear as mud?    

                       

                      Let us know if you still have trouble.

                       

                      Mike

                      • 8. Re: creating relationships
                        erolst

                        @ Mike

                         

                        If this really is a single table we're talking about, what's the use of copying data from the table via a self-join into “itself”? The copied data have no relation to the record they're being copied into other than the arbitrary selection in a popup, and if I wanted to see the “real” data corresponding to item no xy, I may as well choose Find Mode and type the item no. The method you're describing is sound in itself (and your explanation literally quite instructive!), but isn't the entire scenario screaming “join/child table”? Please correct me if I'm wrong. (I've read your description several times, and I'm still not sure I'm not missing something…)

                         

                        I had assumed (silly me!) - and this is reflected in my example database - that there is a Procedures table with associated data, which will be used to populate an Incident (Consultation aka PatientProcedure?) table, to calculate the cost of one actual consultation; the Procedures table is a kind of Products table, and the Consultation table holds associated line items (and a Patients table is lurking in the wings …).

                         

                        @ Jaclyn

                         

                        IMHO it would be more fruitful if you didn't describe what you're doing in FileMaker or show us raw data, but instead explain what you're trying to achieve. Use your own terms, like “calculate the cost of a consultation”, tell us which things you're dealing with (like Procedures, Patients, Consultations), and what your workflow looks like. In short, let's have a nice brainstorm. As long as we don't know anything concrete and can't see the bigger picture, any further suggestions would at best be shots in the dark.

                         

                        Oliver

                        • 9. Re: creating relationships
                          comment

                          I am not sure what you're trying to achieve either. It sounds like an invoicing solution, where you select items (products) to put on an invoice. If so, I'd suggest you look at the basic demo posted here:

                          http://fmforums.com/forum/showpost.php?post/309136/

                          • 10. Re: creating relationships
                            Mike_Mitchell

                            Oliver -

                             

                            Of course you're right; strictly speaking, the question as presented is a bit puzzling. I don't really know why the database isn't normalized (it should be); hence my comment about normalization earlier. I agree; it should probably be normalized into a join / child table configuration.

                             

                            I was focused more on the mechanics of FileMaker in the answer. Data modeling is a somewhat separate (albeit crucial) issue.

                             

                            Mike

                            • 11. Re: creating relationships
                              erolst

                              Mike -

                               

                              I understand. I found your step-by-step explanation top-notch as a learning tool, but didn't realize that this was merely intended as a demonstration of the techniques involved.

                               

                              We better wait until Jaclyn comes up with more information before we make any other guesstimates.

                               

                              Oliver

                              • 12. Re: creating relationships
                                jacjs

                                Thanks to everyone for their input so far. This is what I am trying to achieve:

                                 

                                I have a list of services (Items) for which each service has a related item number (Item No). Each item number has a related cost amount (Cost).

                                 

                                I have listed these as a 3 column table in excel.Item number eg.jpg

                                 

                                I would like to be able to select the "Item" using the value list as a drop down box and then have the related item number and cost automatically entered into their respective fields.

                                 

                                Please note, I have 3 separate "item" fields, ie. Item; Item2; item3 and their corresponding fields, ie. "item no; item no2; item no3" and "cost; cost2; cost3" for different episodes so I need a solution that can take this into account too.

                                 

                                With the demos posted so far, I can see the file in filemaker but am unable to see how each field, etc is created as when I select "edit layout" and select the field, I cannot proceed any further.

                                 

                                Thanks,

                                Jaclyn

                                • 13. Re: creating relationships
                                  comment

                                  jacjs wrote:

                                   

                                  With the demos posted so far, I can see the file in filemaker but am unable to see how each field, etc is created as when I select "edit layout" and select the field, I cannot proceed any further.

                                   

                                  The demo I have pointed to earlier is completely unlocked.

                                  • 14. Re: creating relationships
                                    jacjs

                                    Unfortunately, I keep getting this pop up when I try to go to manage database, etc

                                    demo.jpg

                                    1 2 Previous Next