AnsweredAssumed Answered

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

Question asked by JeanneCoyneSong on Apr 9, 2014
Latest reply on Apr 9, 2014 by philmodjunk

Like • Show 0 Likes0
Comment • 5

Title

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.

No one else has this question

Outcomes

guaya81 Apr 9, 2014 11:09 AM

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

Actions

JeanneCoyneSong Apr 9, 2014 11:38 AM

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?

Actions

philmodjunk Apr 9, 2014 11:42 AM

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.

Actions

JeanneCoyneSong Apr 9, 2014 1:09 PM

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!

Actions

philmodjunk Apr 9, 2014 3:40 PM

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.

Actions

5 Replies

guaya81 Apr 9, 2014 11:09 AM
Mark Correct
Correct Answer
     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
Like • Show 0 Likes0
Actions
JeanneCoyneSong Apr 9, 2014 11:38 AM
Mark Correct
Correct Answer
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?
Like • Show 0 Likes0
Actions
philmodjunk Apr 9, 2014 11:42 AM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
JeanneCoyneSong Apr 9, 2014 1:09 PM
Mark Correct
Correct Answer
          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!
Like • Show 0 Likes0
Actions
philmodjunk Apr 9, 2014 3:40 PM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions

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

Title

Post

Outcomes

Related Content

Recommended Content