Add a text field and give it this auto-enter calculation:
LengttField & " x " & WidthField & " x " & HeightField
Select "unique values" as a validation option for this field.
Define a field with an auto-entered serial number or Get (UUID) if you do not already have one such field. Use the methods described here to update your existing records to put a value in this field: Updating values in auto-enter calc fields without using Replace Field Contents
Set up a matching field for this value in the table of the layout where you want to see the box cost looked up. Link the two tables in a relationship using this new ID field from your table of boxes to this matching field. Format this matching field with your value list where you select the "use values from a field" option and specify the ID field as field 1 and the new auto-entered calculation field as field 2. You can specify that only the second field be visible for this value list.
Thank you for that. That worked to get the drop down field with the box sizes on my layout. Now, I am trying to get it so when I choose the box size from the field, it automatically fills in the box cost field, using the already inputted data in the other layout. It should be straight forward, so it might just be me having foggy brain at this point in the day. Thanks, in advance, to anyone able to help.
Once you can select the box, you can use a relationship and either a looked up value or calculation option in the field's auto-enter setting to copy the cost into a cost field in the same table as the field you have set up with your value list. You'd want to copy the value here so that future price changes don't cause old records to recalculate and show a new and incorrect total cost.
You can open up the invoices starter solution that comes with FileMaker 11 or later and see how selecting a product copies over a unit cost for that product.
I am still having difficulty getting the box cost to populate. I am sure it is something easy, but my brain gets scrambled as I try to figure it out. Any suggestions on the best way to troubleshoot the issue? I even looked at the invoice starter solution and thought I was doing everything to emulate it, but obviously I am missing something.
What relationship have you defined between the two tables? What are the match fields? You may want to upload a screen shot of Manage | Database | Relationships.
A screen shot of your layout, taken while in layout mode, may also reveal a few clues.
I am sure it may not be the most efficient or even correct way to link some of the fields, but I used an inventory template and altered it to my needs. I do admit it has been years since I have fooled with filemaker and I should've reviewed some of the concepts before I attempted this. But, I just needed to get up and running. Once again, thank you so much for your help.
So your boxes and their prices are in inventory? And you are selecting a box in the ShippingSupplies table?
Or are you selecting a box in Transactions?
So, the box (BoxSizeMerged - field in shipping supplies table) and box cost (BoxTotalUnitCost - field in shipping supplies table) are both in the shipping supplies table and I want the box cost (Shipping_Box_Cost -field in inventory) to show up in the inventory layout when I select the box size used to ship the product (BoxSizeShipped -Field in inventory) .
Boxes and box costs are entered in the shipping supplies in the inventory layout.. Then when I enter a product that sells (in the inventory layout), I want to select the box size from the dropdown menu and have the box cost to populate.
I hope that makes sense.
You need to remove the Inventory::BoxSizeShipped Field--not just from your relationship, but completely from your database as you don't need it.
The relationship should just match this pair of fields:
Inventory::Item ID Match Field = ShippingSupplies::ShippingSuppliesID
I am assuming that ShippingSuppliesID is an auto-entered serial number and that Item ID Match Field is NOT such a field.
If so, your value list should list ShippingSuppliesID as the first field and BoxSizeMerged as the second field.
Item ID Match Field is then the field you would set up on your Inventory table with this value list.
But given the original design of this template, your design changes don't really make sense.
Any given item in your inventory, such as a box of a particular size should be listed in the Inventory only once. There shouldn't be multiple records for the same size box in the inventory table. And thus, the cost of the box would be listed in the Inventory table, not a related table.
ID Match Field IS an auto-entered serial number field - should I change that?
I made some changes and now I lost the box sizes on my layout, it is only listing an unrelated #. I need to retrace some steps to see what happened.
The idea I had with the shipping supplies table, was to input all my shipping supplies as I buy them in a separate table. So, I can relate them to a sold product within the inventory layout, when I sell a product. But, as I said, I was not sure of the most efficient way of setting this up, especially working with the template.
ID Match Field IS an auto-entered serial number field - should I change that?
No, but there are "crow's feet" in a relationship line to that connects to that match field that indicate that this is not the case, so I suggest double checking your field options to make sure that this is really the case.
Let's back up and try again as this new info changes what I was recommending. But first some questions about how you link up inventory items to different boxes: Will each item be boxed separately or will you be packing multiple items in the same box?
It looks like you need to add a ShippingSuppliesID Match Field to Inventory, but your answer to that question could change that detail. If each inventory item is only and always packed in just one size box, one item to a box, then you can add that field and link the ShippingSuppliesID field in ShippingSupplies to this new field. This new field is the one to format as a drop down list or pop up menu for selecting a box for the item.
First, I don't know if this changes anything, but I realized that I incorrectly stated something - I enter the Boxes and box costs in the shipping supplies in the shipping supplies layout.
And I did double check the ID Match Field and it is an auto-entered serial number field. Maybe the "crow's feet" are from the other 2 fields connected to it, but I don't know.
As to your questions; Items will be boxed both separately and sometimes together, if a customer purchases more than 1 item or a multiple of an item. This is for an online (ebay, amazon) business, where I sell new and used misc. items.
So, it sounds like this changes your suggestion?
As to your questions; Items will be boxed both separately and sometimes together,
That suggests that you may have a number of other design issues that aren't part of getting your value list to work, but which will affect how your database is to function and where you link in your table of shipping supplies.
Since, as I thought would be the case, a given inventory item can be packaged in more than one box, I don't see the purpose to linking each item in Inventory to a different single record in Shipping Supplies. Is that what you were trying to do?
On the other hand, you could create one inventory record for each size box and link it to a record in shipping supplies in order to access the details (dimensions, cost) for that specific item. That would be a way to treat each box as another item in your inventory.
What appears missing at the moment are the needed tables to process each online order. Normally, you have a table named Invoices or Orders and a table named Line items for recording and processing each order. See the Invoices starter solution for an example of that.
You may also be interested in this thread where I outline how to combine a typical invoicing system with the Transactions table that you see here in the inventory starter solution such that the same table serves as both the Transactions table and also the line items table so that filling out a customer order automatically removes the ordered items from inventory: