Could be calculation fields that use ExecuteSQl to refer to the values from table 1 for up to 5 different locations.
Delete those fields, you don't need them. Replace them with 1 row filtered portals to table 1. Set up a different filter expression on each portal to filter for only one location.
Thanks for your post!
Option 2 is not an option as need those fields.
I am running FM Pro 11A, and I don't think ExecuteSQL works. Although that would solve the issue.
And why do you need those fields?
I need that info before I suggest an alternative to option 2 that will work with FileMaker 11. (Please let people know what version of FIleMaker you are using. It saves us from suggesting solutions that don't work in your version.)
Thanks again for your reply!
We have 10000+ items. So I don't see how it would work here. I also going to need to export Table2 in excel and show the qty in each location (if any) and the total.
We have 10000+ items. So I don't see how it would work here.
It would work to display the data just fine.
I also going to need to export Table2 in excel
But you'll need the fields for the export to excel.
I'm going to describe how to do this for two locations named "Alpha" and "Beta". I'll leave it up to you to repeat the steps a sufficient number of times to get each of your different fields.
Define a calculation field in Table 2 named constAlpha. Define it with this expression: "Alpha". Define a second field, constBeta as: "Beta". In both calculation fields, select "text" as the result type.
Define these relationships:
Table 2::Item# = Table2|Alpha::Item# AND
Table 2::constAlpha = Table2|Alpha::Location
Table 2::Item# = Table2|Beta::Item# AND
Table 2::constBeta = Table2|Beta::Location
Now you can place Table2|Alpha::qty and Table2|Beta::qty on your layout to show the location specific values of qty on your layout. You can also select these fields from related records when you export this data to excel.
An alternative approach:
Replace the two calculation fields with global text fields in this design. Then assign the values of "alpha" to one and "beta" to the other. This option allows you to change what locations are specified in each column without having to redesign the database, but also requires that you take extra steps to keep the correct values in these global fields--especially if you are sharing this system over a network.
Note: This all assumes that you have just one record in table 1 for a given location and item #. If you have multiple records with the same two values this method will need to be revised to handle that issue.
Thanks again for your reply.
Unfortunately I have trouble understanding what this all mean.
I created the fields, and put "Alpha" and Beta" and in the "specify calculation" and set it as text, but I don't see where I can put those relationships. I can only select fields under "Manage Database ...." --> Relationships, and also don't know how to place these fields on a layout: Table2|Alpha::qty Table2|Beta::qty.
Open Manage | Database | Relationships
Click Table2 to select it.
Click the Duplicate button.
You now have a new Tutorial: What are Table Occurrences? of Table2.
Double click it and you'll get a dialog box where you can rename the table occurrence as I've shown above to get names such as Table2|Alpha.
Then drag from fields in Table 1 to fields in Table2|Alpha to set up the relationship. You can drag from Item# to Item# and then drag from cosntAlpha to Alpha. Or you can double click a relationship line to open a dialog box where you can add or change the match fields used in the relationship.
To add Table2|Beta::qty to your layout:
Enter Layout Mode
Drag and drop from the Field Tool in the status tool bar to add a new field to your layout.
The Specify Fields Dialog box opens.
Select Table2|Alpha from the drop down at the top of the dialog box. Then click qty in the list of fields from that table occurrence to select it for your layout.
Works Great! Thanks for your help!