1 2 Previous Next 25 Replies Latest reply on Sep 11, 2013 11:46 AM by LarsChristensen

    Help with relationships and getting value based on two criteria

    LarsChristensen

      Title

      Help with relationships and getting value based on two criteria

      Post

           Hi,
            


           I’m totally new to Filemaker Pro, and database generation in general for that matter, so please forgive if my question is stupid.

           I’m trying to build a database for a small engineering company with the purpose of keeping a record of the following:

           - Clients

           - Operations

           - Personnel

            

           Each client has several plants at which the operations take place.

           I’ve created a table and a layout for Clients, Operations and Personnel.

           I’ve then given the Clients layout five tabs, one for general information, and four for individual plants.

            

           The general information tab shows the total volume and total price of the all the operations performed on all of the plants of that specific client.

           The individual plants tabs are to show brief information about the operations performed, which I’ve done through the use of a portal and the filter option to exclude wrong plants by using a plant ID field.
           It’s also supposed to show the total volume and price for operations performed on that specific plant, and this is what I cannot figure out how to do.
            

           I've included a picture to help clarify what I need.

            

           Thanks.

      Screen_Shot.png

        • 1. Re: Help with relationships and getting value based on two criteria
          philmodjunk

               Don't you also have a table for each plant?

               Do get a total of records shown in a filtered portal, define a summary field in the portal's table that computes the total of the Price field and then another for the total of the volume table.

               Then enter layout mode and make a copy of your current table. (Copy it so that it has exactly the same portal filter and show related records from setting.)

               Delete the fields from the table and change the number of rows settting to 1.

               Then use the field tool to add your two summary fields to this one row portal and they will disply the total just of the records in the filtered portal.

          • 2. Re: Help with relationships and getting value based on two criteria
            LarsChristensen

                 No, I don't have a table for each plant, as you can see from the picture below all all the fields are stored in the Clients table, because I thought this was necessary to get the all the information in one layout. "address1" is the street name for Plant 1, "address2" is the street name for the second plant and so on and so forth.
                 I sense that this is perhaps not necessary?
                  

                 The Client table is related to the Operations table through a Client ID. I then differentiate between the different plants thorugh a Plant ID, which goes from 1-4. This is perhaps the problem?

                 Would it be better to create a table for each plant?

                 Thanks so much for your help!

                  

            • 3. Re: Help with relationships and getting value based on two criteria
              philmodjunk

                   On which of the above tables is your portal based? Operations? I'm trying to see how you are Filtering a portal for a specific plant.

                   What I suggested about getting a total from a filtered portal should still work even without a table for plants.

              • 4. Re: Help with relationships and getting value based on two criteria
                LarsChristensen

                     Thanks, it worked beautifully.

                     Now I just want to know if I'm crafting my database with "bad" style, for lack of better phrase.

                     The portal is based on Operations, and I'm filtering results by plant ID, which is a field in the Operations table.
                      

                • 5. Re: Help with relationships and getting value based on two criteria
                  philmodjunk

                       In the short term, what you have is fine, but in the long term, you could have issues to resolve.

                       What will you do if you get a client with 5, 6, or 7 plants? Currently, you have to redesign your layouts to add more tabs for more plants. Other layout designs need not be so rigid so that a client can have any number of plants and yet you can still work with them without having to redesign your layout to handle more.

                       Also, while a filtered portal with a summary works fine for displaying the data, it's not a useful method for producing a total that you then need to use in a calculation.

                       One approach is to use a different relationship that matches by both a client ID and a Plant ID field to matching fields in Operations. THen you can select a plant from a drop down list of plants (showing only those for the current client via a conditional value list) and a single tab can be used for as many different plants as a customer might have. And if your relationship is matching by a plant ID value, you now can access those totals for use in other calculations much more easily. You may even find that using a sum function defined in a calculation field in the client table may compute the total with less screen refresh issues if you are editing data in the portal in ways that then requires a new total for these values.

                       Setting up a table for plants where you have one record for each plant may or may not be useful.I would guess that you'd need to document key info specific to each plant such as it's location and a table for this information would then prove very handy for your database and can be linked both to clents and a Tutorial: What are Table Occurrences? of operations.

                  • 6. Re: Help with relationships and getting value based on two criteria
                    LarsChristensen

                         This was immensely helpful.

                         I will definitely create a table for the Plants and move forward from there.

                         Thanks for the help, much appreciated!

                    • 7. Re: Help with relationships and getting value based on two criteria
                      LarsChristensen

                           Now I've created a table for the plants, and my relationships are as pictured below.

                           I've changed my "Clients" layout so that it contains one "General information" tab and one "Plants" tab. I've then created the dropdown menu to select the different plants, but when I do, it does not change the content of the tab. It changes the number in the dropdown menu, but has no effect at all on the the rest of the tab.

                           What am I missing here?

                           Thanks!

                      • 8. Re: Help with relationships and getting value based on two criteria
                        LarsChristensen

                             Correction: I can get everything but the Web Viewer field (google maps) to be affected by a change in Plant ID in the dropdown menu.

                             However, and this is also a bit annoying, I have to click beside the dropdown menu once I've selected the plant ID for it to take effect.
                             Is there a quick fix to this?

                             Thanks!

                        • 9. Re: Help with relationships and getting value based on two criteria
                          LarsChristensen

                               Okay, this has to be getting tiresome, I realize, but bear with me.

                               I've figured out that the problem is that I am not just choosing which Plant to show when using the dropdown menu, but I'm actually also changing the ID number for the plant each time I use the dropdown menu.

                               It's very weird and confusing.

                          • 10. Re: Help with relationships and getting value based on two criteria
                            philmodjunk

                                 First, the relationship from plant operations to Plan need only match on Plant ID. Including the client ID fields as match fields is not necessary.

                                 It sounds like you have set up the Operations::PlantID field as a drop down and thus, when you select a different ID, you are indeed changing which Plant record is linked to that Operations record.

                                 I am assuming that each Operations record records data specific to one client and one plant and that you will be creating additional records to log additional info for operations on each plant.

                                 Set up this relationship:

                                 Clients::Client_ID = Operations::Client_ID AND
                                 Clients::gSelectedPlantID = Operations::Plant_ID

                                 Make your portal a portal to Operations and set up gSelectedPlantID as your drop down. I'd make gSelectedPlantID a field with global storage (That's why I started the name with a "g"--my method of labeling fields for which I've set up global storage.), but it is not absolutely necessary to do so. You may eventually find it useful to specify a sort order for the portal that lists the most recent operations record first to reduce the need to scroll the portal to see most recent records or to add a new record. (A button can be added that creates a new related Operations record and with the correct portal sort order, the newly created record will appear as a blank row at the top of the portal.)

                                 And keep in mind that you can have more than one relationship between clients and operations. By using two different Tutorial: What are Table Occurrences? of Operations, you can have one relationship that only matches by ClientID and still have the above relationship that matches by both ClientID and a selected plant ID.

                            • 11. Re: Help with relationships and getting value based on two criteria
                              LarsChristensen

                                   Once again, hugely beneficial. Thanks so much.

                                   About this: "First, the relationship from plant operations to Plan need only match on Plant ID. Including the client ID fields as match fields is not necessary." 
                                   
                                   The plant ID's are not individual from client to client, they are actually more like plant numbers, so to speak. Wouldn't this make the match necessary? 
                                   One a different note, perhaps I should make the unique, because it might be beneficial in terms of graphing the most valuable plants?

                              • 12. Re: Help with relationships and getting value based on two criteria
                                philmodjunk

                                     The plant ID should be an internally generated serial number that IS unique for each plant. Any externally supplied plant name or number can be included as a field in your table, but don't use it as a match field in your relationships. Value lists can be set up so that you see the externally supplied plant name or number that the user recognizes, but the ID that is entered would be the unique serial number.

                                     And I did mention that a conditional value list can be set up so that you only see the list of plants in the drop down list for the current client. Here are some links on conditional value lists:

                                     There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                                     The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                                     Forum Tutorial: Custom Value List?

                                     Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                                     Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                                     Hierarchical Conditional Value lists: Conditional Value List Question

                                • 13. Re: Help with relationships and getting value based on two criteria
                                  LarsChristensen

                                       Thanks, I will try to convert the Plant ID, and client and operator ID for that matter, to a auto-enter serial number, this just means I have to fiddle a bit with my test records.

                                       I'll also try to crack the conditional value list, but this has been a big problem for me, so it's not first priority.


                                       Another thing: Would it be possible to do a "data entry" layout for Clients, Operations, Plants and Operators, and then a "browse" layout for the same tables where the records could not be changed? 
                                       And if so, how do you lock the records for entry?

                                       Thanks again for the assistance!

                                  • 14. Re: Help with relationships and getting value based on two criteria
                                    philmodjunk

                                         You can make as many layouts that refer to the same table occurrence (box in Manage | Database | Relationships) as you need. You also have a "duplicate layout" option in the layouts menu when you are in Layout Mode that you can use to generate a new layout that is a copy of the current layout.

                                         In layout mode, you can select a field and then use the Behavior settings on the Inspector's data tab to deny access to the field when in Browse mode. You can also use different privilege sets for different users in Manage | Security and then one user can have a privilege set that allows them to edit data and another that is limited only to viewing the data. That approach can eliminate the need for two layouts in some cases.

                                    1 2 Previous Next