6 Replies Latest reply on Dec 26, 2013 1:48 PM by SteveGruskin

    Constructing a description based on matching a field within one table

    SteveGruskin

      Title

      Constructing a description based on matching a field within one table

      Post

           I am working on an inventory system and would like to create a descriptive field made up of several other fields in the same table.  For instance, each piece of equipment is assigned to a case.  That case has a name and a bar code.  I can already easily list out the contents of each case by sorting by case name.  However, I would like to create a field that contains a description, made form a calculation of adding values from various other fields (manufacturer + item + model #), which would then be referenced when that case bar code is scanned.  The equipment sign out is tracked in another table that relates back to the inventory, so when a case is scanned, the case name comes up, but it would be nice to see a description of what is also in the case.  I can't figure out the calculation that will put together the description in the first table though.  Any advice will be well appreciated.

           I hope this makes sense.... Thank you!

        • 1. Re: Constructing a description based on matching a field within one table
          philmodjunk

               A calculation expression such as: Field1 & " " & Field2 & " " Field3 can certainly combine data from multiple fields, but I don't see a need for such a calculation here.

               Assuming that you have this relationship:

               SignOutTable::Barcode = Inventory::BarCode

               Then you can add any needed fields from Inventory to your SignOutTable layout and the fields will show the data from the matching record in Inventory once you scan in a matching bar code. Thus, the individual fields in Inventory for: manufacturer, item, model # can simply be added to your layout.

          • 2. Re: Constructing a description based on matching a field within one table
            SteveGruskin

                 Thanks for the quick reply.  I already have the relationship you mention set up, but my problem is that there are several items within the case, each with it's own record, and I'm trying to incorporate that info into the SignOut form.  Right now, when the case is checked out, the case name comes up on the sign out sheet based on a simple lookup.   I would also like to see a listing of the items in the case (all of which are part of the inventory table but as separate records).  This is why I thought it would make sense to create a single calculated field of items that are in the case as part of the record for that case, and then add that single field to the SignOut layout.  I think the issue is that I am trying to pull info from several different records within the Inventory Table together into one SignOut record.

                 Would it make sense for the cases to be part of a different table? (Not sure why I am asking this, but it feels like there are different layers to what I'm trying to do). 

                 Sorry if this is confusing.... I used to use Filemaker a lot for simpler applications, but it's been a number of years and I'm trying to wrap my head around this.....

                 Thanks very much, I really appreciate your response.

            • 3. Re: Constructing a description based on matching a field within one table
              philmodjunk

                   It would help to explain what you mean by a "case" and how the data you want to see is structured in the related table. There are functions such as List and ExecuteSQL that can pull together data from multiple related records and a portal is also a possible option here for listing multiple related items.

              • 4. Re: Constructing a description based on matching a field within one table
                SteveGruskin

                     OK -- here goes. This database is for a video production company that produces television series, and we own a lot of equipment.  We have so much stuff, and four different series going, that we decided we need to track everything carefully.  I have used Filemaker in the past for other purposes than inventory, so I volunteered to try to make this work.

                      By a "case" I"m referring to the physical box that contains various pieces of equipment.  For instance, Case 1 might have a camera body, a filter set, wireless transmitter/receiver, rails, etc.... -- pretty much everything needed for that particular camera.  Case 2 might be audio gear like microphones, transmitters and receivers, boom pole,  Case 3 could be lenses, etc...   The major items in each case are bar coded and have been entered into the Inventory Table.  Each item has a record with info including the manufacturer, model #, where purchased, repair records, etc...  The cases are also bar coded and included in the Inventory table.

                     Each piece of Equipment in the Inventory table is assigned to a Case via a "Case" field, and I'm able to sort by case and generate an inventory list showing what is in each case.

                     I've made a separate table called SIgnOut with a layout that allows the user to scan up to 10 cases when they are checked out.  This also tracks the out date, the in date, which vehicles are used, which series it's going to, and so on.

                     Right now, when a case is scanned, the number and name of the case comes up (i.e. Case 01 - F5 camera).  I would like to have a more detailed description in one field, along the lines of:  Sony F5, rails, Bolt #1 transmitter, Bolt #1 receiver, filter set.  This could be put together with a text calculation as it simply a listing of items that are included in fields for each piece of equipment in the Inventory Table.

                     I hope this explains what I'm trying to do a little more clearly.  

                     Again, I appreciate your advice -- I'm not an expert programmer (obviously) but enjoy the challenge of making this work.

                      

                      

                      

                • 5. Re: Constructing a description based on matching a field within one table
                  philmodjunk

                       This sounds like something that could be handled with a portal where the portal lists the contents of the items inside the case. The actual organization of your data appears to be like this:

                       CheckInOut-----<Cases------<CaseContents

                       The fact that Cases and CaseContents are both records in the same table does not change that  relationship.

                       A list view layout based on Cases could combine data from both your CheckInOut and CaseContents tables.

                       But a calculation using List or ExecuteSQL could also list the CaseContents for use as a field in a portal to cases placed on the CheckInOut layout.

                  • 6. Re: Constructing a description based on matching a field within one table
                    SteveGruskin

                         That all makes sense -- although I will have to read up on portals.  Thanks again!