5 Replies Latest reply on Aug 13, 2014 10:22 AM by philmodjunk

    Calculation to Decrease/Increase Number by 1

    SydneyBlock

      Title

      Calculation to Decrease/Increase Number by 1

      Post

      I am creating a database for potential job applicants that uses an alternate table to record experience. The tables are linked by a relationship between:

      StaffApplicants::xp_AppID  & StaffApplicants::g_ExpNum

      to

      Experience::xF_AppID  &  Experience::AppExpNum

      Whenever a user clicks "Add," the fields from the related table will clear to add another record into the table. The “Add” button would have a script that increased Applicant::g_ExpNum by one. The fields would all immediately go blank for the new record and the relationship would automatically populate both the xF_AppID and AppExpNum

       

      Would this script be "Set Next Serial Value"? I can't seem to get it to work. I also need to add checks to the previous/next buttons that won’t let you go below 1 or over the highest value stored for that applicant.

            

            

        • 1. Re: Calculation to Decrease/Increase Number by 1
          philmodjunk

               Set Field [Applicant::g_ExpNum ; Applicant::g_ExpNum + 1 ]

          • 2. Re: Calculation to Decrease/Increase Number by 1
            SydneyBlock

                 Thank you so much! It worked perfectly :)

                 Now my last step is to create previous/next buttons that won't let the user go below 1 or over the highest value stored for that applicant. I want the applicant to be able to see their previous entry if they need to make any changes.

            • 3. Re: Calculation to Decrease/Increase Number by 1
              philmodjunk

                   You'll need a relationship to a different Tutorial: What are Table Occurrences? of Experience that only matches by App_ID to use to determine the current Max value of AppExpNum.

                   Applicant::xp_AppID = Experience|IDonly::xf_AppID

                   Then you can put this expression into the second parameter of the Set Field step:

                   Case ( Applicant::g_ExpNum = 1 ; 1 ;
                             Applicant::g_ExpNum =Max ( Experience|IDonly::AppExpNum ) ; Max ( Experience|IDonly::AppExpNum ) ;
                             Applicant::g_ExpNum + 1 // Else
                            ) // Case

              • 4. Re: Calculation to Decrease/Increase Number by 1
                SydneyBlock

                     Why would this require another table occurrence of the Experience table if that is the table the records are being put into? Wouldn't it have trouble determining the max value if it doesn't have any records in that table?

                      

                     Sorry, just trying to wrap my head around all of this.

                • 5. Re: Calculation to Decrease/Increase Number by 1
                  philmodjunk

                       First, a table occurrence is not another table, it's a reference to the same table that you can use to set up a different relationship to that same table of records.

                       If you had the values  23 and 2 in your Applicant::xp_AppID and Applicant::g_ExpNum fields respectively, your relationship matches to either no record in Experience or just 1 record. The Max function will then return either Null (no records) or the value of AppExpNum for that one record--the number 2.

                       When you set up a new relationship to a different occurrence of this same Experience table, that matches only by Applicant ID, this matches to All the Experience records for that applicant and then Max can return the largest AppExpNum value of all the related records that currently exist for this applicant.