5 Replies Latest reply on Aug 15, 2011 2:34 PM by ijontichy

    Tracking a "batch number"



      Tracking a "batch number"


      I'd like to think this is an easy one.

      I have several records that get brought in at once and I'd like to assign them a "BATCH" number that is 1 higher than the previous batch.

      Easy right?   The issue is that not every record has been assigned a batch number.   (Other people entering records or other issues).

      So while it seems like it would make sense that a Summary field asking for the maximum of "Batch" would tell me the value I need to beat...

      If I happened to be parked on a record without a batch number assigned (like after importing the newest batch, for instance), the summary returns no value.

      I should mention that I've set up a duplicate table and a "not equal" to relationship and am reading the summary from there so that no matter what my found record set is I'm always looking at all the records in the table for this value.


      What am I doing wrong?

      I'd love to be able to import a bunch of records, ask the database what the highest batch number is and then give this new import that highest batch number plus 1.   via scripting.


      (or, put another way, just before importing a bunch of records I want to ask the database what the highest batch number is, then import these new records and assign them a higher number automatically by scripting.


      Thank you in advance:   The knowledge base here is phenomenal.  

        • 1. Re: Tracking a "batch number"

          You may want to try using X operator instead of not equals. With not equals, you need some value in the field on your current record before it will link to records in the other table.

          • 2. Re: Tracking a "batch number"

            I've used the X connector and no change in effect.   If I'm parked on a record with a blank scan number, the summary that is being pulled from the unsorted instance of the table still shows up as blank.


            Any other thoughts?

            • 3. Re: Tracking a "batch number"

              I have to admit to a mea culpa.   Phil's first answer is correct.   I had some conditional formatting that I'd copied from my layout and forgotten about which was messing me up further.   But even getting rid of that I had issues until I chose the X relationship.


              Sorry if you scratched your head too much on my behalf, Phil.

              • 4. Re: Tracking a "batch number"

                You "unscratched" before I got back to this thread so my follically challenged pate is undisturbed. Wink

                However, I've had second thoughts about my too short answer. If this is a hosted database where two users might do this at the same time, you risk problems with two batches getting the same number. It would also be good database design to add a related table where you have one record for each lot.

                Set up a script that goes to a layout based on this new "lots" table, creates a new record in it, copies the serial number value of this new record to a variable, then returns to your original layout to use Replace Field Contents to copy the value in this variable into all the records in your found set.

                • 5. Re: Tracking a "batch number"

                  Elegant, as usual.  Thanks again, Phil.