1 2 Previous Next 17 Replies Latest reply on Jan 17, 2014 10:08 PM by BradSand

    How can I combine data from 3 fields into a lookup list for a different field, etc.

    BradSand

      Title

      How can I combine data from 3 fields into a lookup list for a different field, etc.

      Post

           Ok, so here's what I am trying to do:

           I have a layout where I input shipping box sizes into 3 separate fields (length, width, and height) and then I input the box cost in another field, and have another field which calculates the total box cost, including tax.  This way, I have the size of the shipping box associated with it's unit cost and have the ability to search the boxes by any of the dimensions.

           So, what I am trying to do is - in another layout (call it "sales" layout), have one field with a drop down list of the combined box dimensions (length x width x height) so that when I select the appropriate size box for the product I am shipping, I can have the box cost field auto populate, in order to include those costs in my shipping costs for that item.

           example

           "shipping supplies" layout has 3 box fields showing 12 x 12 x 6

           1 "box cost field" showing $1.25 (cost of a 12 x 12 x 6 box)

           Then when I go to the other layout, I want to be able to have a drop down list of all my in-stock shipping boxes, in 1 field, so I can select the appropriate box and have the "box cost field" populate automatically.

           selecting 12 x 12 x 6  ... would populate $1.25 in the "box cost" field

           selecting 12 x 15 x 6  ... would populate $1.50 in the "box cost" field

           selecting 15 x 15 10  ...would populate $1.75 in the "box cost" field

           etc....

           I'm not sure if I am over-thinking this, or if there is a better way to do it.  I am a novice with filemaker, so any direction is appreciated.

        • 1. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
          philmodjunk

               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.

          • 2. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
            BradSand

                 Phil,

                  

                 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.

            • 3. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
              philmodjunk

                   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.

              • 4. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                BradSand

                     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.

                • 5. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                  philmodjunk

                       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.

                  • 6. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                    BradSand
                    /files/9164a06ea9/Screenshot_of_filemaker_inventory.jpg 1243x557
                    • 7. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                      BradSand

                           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.

                      • 8. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                        philmodjunk

                             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?

                              

                        • 9. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                          BradSand

                               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.

                          • 10. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                            philmodjunk

                                 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.

                            • 11. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                              BradSand

                                   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.

                              • 12. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                                philmodjunk
                                     

                                          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.

                                • 13. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                                  BradSand

                                       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?

                                  • 14. Re: How can I combine data from 3 fields into a lookup list for a different field, etc.
                                    philmodjunk
                                         

                                              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:

                                    Managing Inventory using a Transactions Ledger

                                    1 2 Previous Next