5 Replies Latest reply on Apr 9, 2014 3:40 PM by philmodjunk

    Newbie question, how to auto-fill a field based on another field

    JeanneCoyneSong

      Title

      Newbie question, how to auto-fill a field based on another field

      Post

           I know this is super simple, but I am quite new to Filemaker and work at a school without a tech person.  I'd like to save money by not hiring our consultant to create this field.
            

           We have a database to track auction items.  These items fall within specific categories, i.e.

           100: Clothing, Jewelry & Accessories

           200: Art, Books & Music

           300: Pamper Yourself

           And so on.

           These are in a value list that are a drop down menu for the field called Donation Category.  I created a field Donation Item Number.  It would be great if this field could take the numbers from the first field and auto-populate.  So, if I enter a hand-sewn jacket as an item, choose the category 100: Clothing, Jewelry & Accessories, the Donation Item Number would fill in automatically with 101, and so on.  The list goes all the way up to 1400: From the Faculty.

           We aren't so large that this couldn't be done manually, but it would be super snazzy and a good time-saver as we use volunteers for all of the data entry. 

           Thanks for any help from an underpaid, overworked and confused non-profit worker.

        • 1. Re: Newbie question, how to auto-fill a field based on another field
          guaya81

               you can create or in the same field, make a calculation type field and do a if formula and else formula for that if you send me some example i will be glad to halp you making the formula

                

               thanks

          • 2. Re: Newbie question, how to auto-fill a field based on another field
            JeanneCoyneSong

                 Thanks Erik.  I'm not sure what example I could send to you?  I've taken a screenshot of the data entry form.  At the bottom blue section, you can see that the Donation Category is filled in (using a drop-down) and the field next to it, Item Number is empty.  I'd like that field to fill in based on the first 3 or 4 digits from Donation Category.  Should I set the Item Number field as a number, then go to Options? Under the Auto-Enter I can see that there is a Calculated Value, but I'm not certain how to write the Specify Calculation.  Or is there another way to do this?

            • 3. Re: Newbie question, how to auto-fill a field based on another field
              philmodjunk

                   With a self join relationship, you can match records by category and then Max ( RelatedTableOccurrence::serialnumber ) + 1 will provide the next number in the series.

                   But if you have multiple people adding/editing records at the same time, there's a chance that two records could get the same number. If you have only oe person doing this, it's not an issue.

                   I'd use a script to assign this value and it can include code to set the initial value when there are no previous records of the same category.

              • 4. Re: Newbie question, how to auto-fill a field based on another field
                JeanneCoyneSong

                          Soooo...from what I am reading and based upon my capabilities, a script sounds like the best option.  Once all of the items are entered, I can have a script sort based on category, then assign numbers from there.  We will have multiple people doing data entry, so I can't be assured of redundancy. 

                     I'm thinking a script such as the following?  I would repeat this for each donation item category, of which I have 14.  Does this make sense?

                     Please note that I have no formal training in Filemaker and I have figured this out on my own without any computer background, so much of what you say I have to look up and figure out.  Simpler is better for me as well as being explicit.  Thanks so much!

                      

                • 5. Re: Newbie question, how to auto-fill a field based on another field
                  philmodjunk

                       You should only need one script, not one for each category.

                       Replace Field Contents modifies the field for all records in your found set so this seems likely to change records to values in ways that won't work for you and don't you have all these items from all categories in a common table? that would make the combination of Show All Records and Replace Field Contents especially catastrophic--especially if two or more people do this at the same time as it would result in frequent errors due to edit locks on individual records.

                       What I suggested would use a self join relationship such as:

                       TBSAuction::Category = TBSAuction|SameCat::Category

                       where TBSAuction and TBSAuction|SameCat are two Tutorial: What are Table Occurrences? with the same data source table.

                       This sets up a script for assigning an auction number like this:

                       Set Variable [$Maxvalue ; Max ( TBSAuction|SameCat::AuctionNumber ) ]
                       IF [ $MaxValue = 0 // first item in this category ]
                           Set FIeld [ TBSAuction::AuctionNumber ; Case ( // put calculation here that uses category to determine the initial value for that category) ]
                       Else
                           Set Field [ TBSAuction::AuctionNumber ; $MaxValue + 1 ]
                       End If

                       This auctionNumber should not be used as a primary key to link records in TBSAuction to other tables. Use an auto-entered serial number field for that purpose and an unique values validation option should be specified for AuctionNumber to ensure that the values given to it are unique.