1 2 3 Previous Next 38 Replies Latest reply on Feb 21, 2013 1:08 PM by philmodjunk

    Increase Value of Field on Another Table

    J.S._1

      Title

      Increase Value of Field on Another Table

      Post

           I have a field called Proposal_Number on my table called Proposals, which is populated based on a calculation that includes the two-digit numeric value of the Rep_Count field on the Reps table. I need the value of Rep_Count to be increased each time a proposal is commited, so the next proposal number would include the increased value. The other requirement is that I need the value of Rep_Count to reset to a value of 01 each day. Thanks!

        • 1. Re: Increase Value of Field on Another Table
          schamblee

               You can have a auto enter serial number field then click generate on commit.  To start back at zero just run a script to set field to 0.  The easies way is for someone to manually run this script each morning.  To automate you would have to run a startup script to verify that the date has changed and have a flag that is set to show that the script has been ran for that day. 

          • 2. Re: Increase Value of Field on Another Table
            J.S._1

                 Ok, perhaps I am overlooking something...

                 1. I set the Rep_Count field, which is a number field, on the table Reps to auto generate serial on commit. The next value is 1 and the increment value is the same.

                 2. I have a table occurance of the Reps table, called ProposalsReps, connected to my Proposals table, linked by the field Rep_Name. For the relationship I checked "Allow creation of records in this table via this relationship" on the PropsoalsReps side of things.

                 3. I placed a button on the Proposals form that is labeled save and has the function "Commit/Records Requests".

                 When I fill in the necessary fields on the Proposals form and click the 'Save' button to commit, then start another record, the value from Rep_Count remains 01.

            • 3. Re: Increase Value of Field on Another Table
              J.S._1

                   ::Bump::

              • 4. Re: Increase Value of Field on Another Table
                philmodjunk

                     Since the Rep_count needs to increment each time you create a new proposal, an auto-entered serial number would need to be defined in the proposals field, table, not the Reps field. table.

                     Can you describe what exactly you are trying to do with this field and why? It's possible that a completely different approach based on a relationship that matches by date will do what you need.

                • 5. Re: Increase Value of Field on Another Table
                  J.S._1

                       Proposal numbers are generated based on a few different factors: sales rep, date, and daily count.

                       So, for example, if I am a sales rep, and I am creating a proposal today, 2-17-13, and it is the 5th proposal I have created today, then the proposal number would be: JS021705-13; REPINITIALS+MM+DD+COUNT+ - +YY.

                       On the Proposals layout, the proposal number is automatically calculated. The initials and the daily count come from a table called Reps. I need for the daily count to increase by +1 for each new proposal created by a rep. Since this is a daily count, it would need to reset to 01 each new day. You can see this functionality on the Proposals layout of the file I sent to you.

                  • 6. Re: Increase Value of Field on Another Table
                    philmodjunk

                         Note that there were errors in my previous post that I have now corrected.

                         I hope you are not trying to use this field as your tables primary key for use in relationships as this is not a good idea. Use a separate auto-entered serial number for that purpose and keep this Proposal "number" field as just a data field in your proposal table.

                         I don't see a need for that related daily count table.

                         Define Rep_Count as an auto-entered serial number field in the Proposals table. Use a script to kick in, say at 12 midnight--hopefully this is not a database being used 24-7, to reset the serial number field's next serial value back to zero.

                         If you host the database with FileMaker server, a server schedule can perform such a script.

                         For files not hosted with server, a robot file can by launched at a specified time via an operating system based scheduler that can perform a script in your file to reset the serial number.

                         If your database is a single user database, you can set a script to reset the serial number the first time that the file is opened each day.

                          

                    • 7. Re: Increase Value of Field on Another Table
                      J.S._1

                           The daily count is on a per-rep basis. If we are both sales rep, you could be on proposal number 05 one day, and I on 02.

                           The Reps table also stores contact information for the sales reps, as well as their initials for use in populating the beginning of proposal numbers. When a rep is selected from the Rep Name pick-list on the Proposals layout, a number of fields are populated, based on the selection.

                           I have set the rep_count to auto-enter serial, but I still need a way to commit the record, so that the value increases by an increment of +1.

                      • 8. Re: Increase Value of Field on Another Table
                        philmodjunk

                             Then an auto-entered serial number won't work for this.

                             Use a script to create proposal records instead of the standard new record menu option:

                             New Record/Request
                             Set Field [Reps::Repcount ; Reps::Repcount + 1 ]

                             Set up a relationship between Reps and Proposals that looks like this:

                             Reps-----<Proposals

                             Reps::AccountName = Proposals::AccountName

                             Set up Proposals::AccountName as a text field that uses this auto-enter calculation to link it to the correct record in Reps:

                             Get ( AccountName )

                             This method assumes that Proposals are only created by the Rep that has logged into the database to create the Proposal record.

                        • 9. Re: Increase Value of Field on Another Table
                          J.S._1

                               I already have the relationship setup...

                               Proposals::Rep_Name = ProposalsReps::Rep_Name

                                

                               Would the auto-enter calculation then need to read...

                               Get ( Rep_Name )

                                

                               "This method assumes that Proposals are only created by the Rep that has logged into the database to create the Proposal record."

                               Does this mean that I need to somehow associated the individual record on the Reps table to the logged-in user?

                          • 10. Re: Increase Value of Field on Another Table
                            philmodjunk
                                 

                                      Would the auto-enter calculation then need to read...

                                 

                                      Get ( Rep_Name )

                                 That would not work and linking records by their name is not a good idea. People's names are not unique and they change their names--both issues you can avoid if you don't match them by name.

                                 

                                      Does this mean that I need to somehow associated the individual record on the Reps table to the logged-in user?

                                 I'm explaining an assumption on which I based my suggestion. I am assuming that Reps enter their own proposal records and only their own proposal records. If so, then add a field for their account name to Reps and use it in the relationship to link Proposals to an occurrence of Reps for use in this script. And yes, this does link each Proposal record to a specific record in Reps.

                                 Oh yes, and this field in Reps would need to be set back to 0 in every record via a script at the end of each working day.

                            • 11. Re: Increase Value of Field on Another Table
                              J.S._1

                                   Ok, I am beginning to understand your aim on this, but I am still confused on how the script is integrated into the mix.

                                   I have a calculation on the Proposal_Number that is grabbing the value of Rep_Count for the rep when they select their name from the Rep_Name pick-list. I realize that at some point the script will be executed and it will add +1 to the rep_count field, but where does it get executed. Also, what happens if the user initially "executes" the script, but then does not follow-through with the creation of a new proposal. Will the rep_count value be rolled back by -1?

                                   

                                        "I hope you are not trying to use this field as your tables primary key for use in relationships as this is not a good idea."

                                   I am not. I have two fields that might be confused with one another at first glance, Proposal_ID and Proposal_Number. The Proposal_ID is an auto-enter serial field and is not seen by the user. It is the unique-ID field. All of my tables have a similar field based on their own name: Account_ID, Contact_ID, etc.

                                    

                              • 12. Re: Increase Value of Field on Another Table
                                philmodjunk

                                     The script I suggested both creates the new proposal record and also "bumps" the count field up by one at the same time. It avoids needing the user to select from a drop down--which would happen after this step because of the assumptions that I mentioned ealier. I can't tell from your posts whether those assumptions will work for your actual situation or not.

                                     The method that I outlined would not automatically roll back the number if the user then deletes or reverts to cancel out of creating a new proposal.

                                     Hmmm, seems like a different approach may work better as long as you don't have two users creating proposal recors for the same rep at the same time:

                                     Define a calculation fieldin Reps, cMaxCount, in Reps as Max ( Proposals::Repcount )

                                     Define cToday as an unstored calculation field: Get ( CurrentDate )

                                     Use this relationship between Reps and Proposals:

                                     Reps::cToday = Proposals::CreationDate AND
                                     Reps::RepID = Proposals::RepID

                                     A RepCount field in Proposals can then be defined to enter a value with this expression:

                                     Reps::cMaxCount + 1

                                     Be sure to clear the "do not replace existing value..." check box and also clear the "do not evaluate if all referenced fields are empty" check box.

                                     The counts, BTW, will automatically reset with each new day.

                                • 13. Re: Increase Value of Field on Another Table
                                  J.S._1

                                       Somethings not right. The number is not increasing properly. As you will see below, I have a pick-list. When a value from the pick-list is selected, a number of fields are populated. This is when the apropriate count value should be entered into the Proposal_Number field. It just remains at a value of 1 and is appearing upon the creation of a record in the Proposals layout, even if a value in the Rep_Name field is not selected. The other issue is that it is a single digit (1), but should be a double digit (01).

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        


                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                        

                                  • 14. Re: Increase Value of Field on Another Table
                                    philmodjunk

                                         Add a set field step in a script:

                                         Set Field [Proposals::Rep_Count ; ProposalsReps::cMaxCount + 1 ]

                                         Use the OnObjectSave script trigger on your rep names drop down to perform this script.

                                         But matching records by Rep name is not the optimimum design. Think of what you will need to do if one of your reps gets married and changes her name...

                                    1 2 3 Previous Next