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.
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!
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.
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.
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.
This was immensely helpful.
I will definitely create a table for the Plants and move forward from there.
Thanks for the help, much appreciated!
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?
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?
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.
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.
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?
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
Hierarchical Conditional Value lists: Conditional Value List Question
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!
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.