4 Replies Latest reply on Jul 24, 2014 10:58 PM by incybau

    automatic Check number serial values



      automatic Check number serial values


           I can't seem to find a post relating to this and hoping someone can help -- it seems like it could be a simple script but I am not sure....

           I have a table that creates Payment vouchers.  There are three banks to choose where the payment will come from.   I was hoping that once you choose the bank to fund the payment, it can search for the last voucher that the bank was used and just add 1 to the check number to auto populate the check number field.


           Should the script be in the check number field? and which calculations should I be using.  Would appreciate any guidance in any form - thanks!!!!

        • 1. Re: automatic Check number serial values

               Using Go to Record/Request/Page in your script might help. Selecting Last in the specify box would help it get to the last record like you mentioned. Maybe also use an If statement like if bankfield= PNC then Go to layout that displays all the vouchers then Go to Record/Request/Page [last]. Then you may need to add a field to a table that correlates with what you're working on and name it PlusOne. Then you can set it to a number field and auto calculation it to be "last voucher field " +1. 

          • 2. Re: automatic Check number serial values

                 A better method would be to use a relationship that matches by Bank ID and a Max (checkNumber) + 1 expression to assign the next number in the series.


                 Payments::_fkBankID = Payments|SameBank::_fkBankID

                 Where Payments|SameBank is my name for a new table occurrence of your Payments table.

                 Max ( Payments|SameBank::CheckNumber ) + 1 will then return the value of the next check number in the series.

                 You can use this as an auto-enter calculation if you clear both the "do not evaluate if all referenced fields are empty" and "do not replace existing value" check boxes.

                 But then set up an auto-enter text field with: _fkBankID & " " & Checknumber, (clear the do not replace field) with a unique values validation. There is a small chance in a hosted database where two users might get the same check number for the same bank if they do this with just the wrong timing.

            • 3. Re: automatic Check number serial values

                   Thanks so much!  Will try this out right away :-)


              • 4. Re: automatic Check number serial values

                     Worked perfectly! Thank you so much!!!!