11 Replies Latest reply on Nov 29, 2016 4:14 PM by GSalmson

    Conditional Increment


      HI there,

      My solution has a field for department where the work needs to be done. It also has a jobid field which is an auto-increment field. I have a layout for each department which finds a list of jobs which is specific for each department. Using the {RecordNumber} at the beginning of each line, I get a nice little "Row number" for each job.


      What I am trying to do is either use a "Get(RecordNumber)" to catch this number from this layout. Only trouble is that the records are created in a different dataentry layout and so the Get(RecordNumber) returns the record number from that layout.


      Or have another increment field which counts up using some calculation like "If (Department=Sales count up 1) else if department=maintenance leave alone. I presume this would come from a global field, but am not sure how to write this one.


      Please point me in the right direction here.

      Thanks for all the help in advance.

        • 1. Re: Conditional Increment
          Johan Hedman

          Why dont you use Serial Number Field in your layout. Then your work orders will have a unique running number.Skärmavbild 2016-11-24 kl. 08.53.17.png

          • 2. Re: Conditional Increment

            Hi John,


            Thank you for the quick reply. I can use the serial number field, but I want it to be conditional. Of all the jobs logged, I want it to count up only if it is for sales.



            Cheers and thanks

            • 3. Re: Conditional Increment

              I suggest explaining why you want it to work that way.

              • 4. Re: Conditional Increment

                I am sorry, I should have explained that to begin with. I am exporting this data to another piece of software which creates a specific report. Unfortunately I am bound to use that software and the catch is that each day this report has to start again with a numbered sequence of 1 to however many sales there are. I can manually type a number in a field and that works, the confusion comes from having more than one operator doing the data entry. I cannot allow a duplication of that number. I also have to keep the sales and services on the same data entry layout, but am trying to separate them to export the data and number them 1-whatever at that point.


                It is doing my head in.

                • 5. Re: Conditional Increment
                  David Moyer


                  food for thought (from a previous post) ...

                  one part of your solution might include a "public table" - this is a table with a single record and non-global fields for "public" data ... like the current rate of the week.  This table is connected to your other table(s) via a cross-join relationship.

                  All this does for you is allow you to populate a single field (e.g. WeeklyCurrencyRate) which can be accessed by all users, all week.  How you go about populating this field is still a question.


                  • 6. Re: Conditional Increment
                    David Moyer

                    sorry - here's the context of my original post:

                    Time dependent of currency

                    • 7. Re: Conditional Increment

                      I have managed to find away around this. It meant creating a separate layout and and using the Get(RecordNumber) in a script step and not from the Autoenter on record creation.

                      • 8. Re: Conditional Increment

                        If possible, avoid incrementing numbers like this -- whether they be for years or departments. It will become a nightmare.


                        The best solution I have found is to keep the next serial numbers in a table, where each record is for the department (or years). The process then goes:


                        Go to the department (or year) record

                        increment the serial number

                        remember the number

                        create a new job record

                        set the serial number


                        The "increment the serial number" process needs to trap for errors which can happen if somebody else is incrementing the number at the same time. Decide how to handle this -- wait in a loop until the number can be incremented, or end the process and tell the user to try later.


                        This solution is handy when clients want jobs numbered by year. Inevitably at the start of the new year, some people need to enter some records for last year, and other people need to enter jobs for the new year. This cannot be done with a simple serial number field, the only way is to remember the last number for each year.


                        The nightmare happens where somebody creates a job for the wrong department, then wants it transferred to the right department, then the other department has a missing job number and we cannot have missing numbers!

                        • 9. Re: Conditional Increment

                          Hi Vaughn

                          That is the best solution. I like the way it works.



                          • 10. Re: Conditional Increment

                            Here is a method for generating serial number "on demand" that does not have an issue with two people trying to do it at the same time and thus getting the same value in two different records:


                            Define a related table. I'll call it "sales serial numbers". A field in this table will provide the needed unique serial number to the parent table when requested. We'll name that field SalesSerialNumb.


                            Define the fields in the related table as:

                            _fkParentID (number field)

                            SalesSerialNumb (auto-entered serial number)


                            Define a relationship from your parent table as:

                            ParentTable::__pkParentID = SalesSerialNumbers::_fkParentID

                            Allow the "create" option for SalesSerialNumbers in this relationship.


                            When the user enters sufficient data that the new Parent record is identified as a "sales" record, have a script do this:


                            Set Field [SalesSerialNumbers::_fkParentID ; ParentTable::__pkParentID ]


                            This step will create a new record in SalesSerialNumbers each time it is performed for a record in ParentTable that does not already have a related records in SalesSerialNumbers and that event will generate a new, unique serial number each time this happens. You can either just refer to the related field during export or you can use an auto-enter option to copy this value into a field in ParentTable. Set Next Serial Value, is a script step that you can use to reset this serial number field from a script should you need to do so.

                            1 of 1 people found this helpful
                            • 11. Re: Conditional Increment

                              Thanks Philmodjunk.


                              This method worked and I think is the cleanest way of doing it.

                              Thank you for the very clear way of explaining what to do.


                              Best Regards