10 Replies Latest reply on Nov 5, 2009 10:32 AM by philmodjunk

    Linking Fields between Records



      Linking Fields between Records


      Hey Guys.


      I'm rather new to FileMaker Pro 10, and am running it on my Mac.


      I'm designing a database to keep track of the merchandise for the band that I work for. I've done fine getting the layouts designed, and figuring out most of the calculation formulas I need to use. I'm getting stuck when it comes to my inventory tracking though. Right now, each record is a new date. Each record contains the different styles of shirts, sizes, and prices for all our merchandise. It also tells me the starting inventory for each piece, and the ending inventory. But I need a new record, let's say tomorrow's date, to pull the inventory number from the last record. So basically, when I create a new record and specify the shirt, I need it to look up the last date's ending inventory for that shirt. 


      Any help would be great! I haven't gotten into any scripts or any of the more intense formulas, so please make sure the answers are "newbie friendly."


      Thanks! =)

        • 1. Re: Linking Fields between Records

          There are several ways to set this up. One method is described in the following thread:




          See if it helps give you some ideas.

          • 2. Re: Linking Fields between Records

            I'm not sure what exactly your going for but your record tracking seems almost redundant.  You say your records are set up by date. Seems to me you should set up your records by "Item Type". Instead of having to create a new record every day to track the difference in inventory set up a few fields to track outgoing shipments, and some for incoming shipments. Then you could set up a final field as a calculation. Have it add up all your incoming merchandise and then subtract your outgoing fields. The end result should be an updating field of your leftover inventory for each record. From there you could set up a report to look display all you merchanise and remaining inventory.  Hope that wasn't to confusing. :)

            • 3. Re: Linking Fields between Records

              Phil - I understand the concept of a log file, I'm just not sure how to apply it in my situation. I'll already be filling in one record of my sales, and don't want to have to go to another table to make another record just to track inventory. Can you show me some other methods?


              Sater - I can see why it looks redundant. Let me explain a little further: The merchandise is sold at concerts, not in a store. Each record is a different venue/show date. If I was just dealing with a few sales a day, or only a few items, your method would make perfect sense. However, I'm dealing with lots of different pieces and lots of sales every night. I keep track of the inventory so I know when to reorder, but I also have to be able to send management a breakdown of each show date so they can see what we sold. I appreciate your response though!

              • 4. Re: Linking Fields between Records

                I think you can do it just by simplifying the technique described in that thread. It doesn't sound like you need price list or invoice tables, just a running log of items on hand.


                Just put in separate fields for "Items In" and "Items Out", Then a summary "total of" field summarizing cBal (Items In - Items Out) can give you the total on hand for all your items. You get this last result by labeling each item consistantly (Use a value list and a drop down or pop-up menu), then sort them first by Item then by date.

                • 5. Re: Linking Fields between Records
                     Ok, that can work. Is there a way to set it up so that when I make a new record, it automatically pulls the ending inventory number from the last record and puts it as the starting inventory number for the new record?
                  • 6. Re: Linking Fields between Records

                    Define the summary field as a "running total" and "restart summary for each sorted group".


                    If you set up your layout as list view, you can get a layout that looks much like a check register with the summary field running down the right to show current quantity on hand.

                    • 7. Re: Linking Fields between Records

                      For a new record, add a a OnObjectSave Script trigger to your item_type field.  There are a couple ways you can probaly do this but The first way i thought of i will say even though it might be longwinded. 

                      SetVariable $$END_INV, 0)

                      SetVARIABLE $$ITEM_TYPE, YourTable:Item_type) 

                      Goto Record(First)


                       If ($$ITEM_TYPE notequal YourTable:Item_Type)

                         omit record


                       Goto Record(Next, Exit after Last) 

                       end if 

                      end loop

                       Goto Record(Previous)

                        SET_VARIABLE($$END_INV, YourTable:END_INV)

                       Goto Layout(Original)

                        SET_FIELD(YourTable: START_INV, $$END_INV)



                        Something like that anyway, after you enter in the item type into your new record it will automatically run your script for capturing that data

                      • 8. Re: Linking Fields between Records

                        With the running total approach, a script is unecessary. The totals for each item type will compute automatically as long as you keep the records properly sorted.

                        • 9. Re: Linking Fields between Records
                             Could you walk me through how to set up that script? I haven't worked with them before, and staring at the screen hoping it'll make sense doesn't seem to be working. =/
                          • 10. Re: Linking Fields between Records

                            No script is necessary--what I describe is all set up with field definitions and how you design your layout. You could set up a script to sort the records instead of selecting sort from the Records menu and you could add a button for creating a new record for the same item as your current record I suppose...


                            But you can also do it all without any scripts.