MISSING RECORDS

Anthony.Kariuki

Forums

When greenleaf is bought at the buying centre, each weighing is given a unique transaction number. This transaction number works like a digital receipt and helps track who bought the leaf, when it was bought, and in what order.

A transaction number is made up of several parts joined together:

Example:
968_07670_0_28052025080619

What Each Part Means (Broken Down)
968 | 07670 | 0 | 28052025 | 080619

1) 968 – Employee Number
This tells us which clerk (Leaf Assistant) bought the greenleaf.
In this case, employee number 968 handled the transaction.

2) 07670 – Running Count (Receipt Number)
This is a sequential counter, like receipt numbers in a receipt book.
It increases by one number each time leaf is weighed.
Example:

07669 → previous transaction

07670 → this transaction

07671 → next transaction

This helps confirm that no transactions are missing or skipped.

3) 0 – System Control Field
This is a system-generated separator/control value used by the system.
It does not change the meaning of the transaction and is mainly for system processing.

4) 28052025 – Transaction Date
This shows the date the leaf was bought:

28 → Day

05 → Month (May)

2025 → Year

So the transaction happened on 28th May 2025.

5) 080619 – Transaction Time
This shows the exact time the weighing happened:

08 → Hour

06 → Minutes

19 → Seconds

So the leaf was weighed at 8:06:19 a.m. How can i go about to test where receipt (running number is missing based on the staff number. expectation is that the number should run from 1 -10000 then reset to one once the receipts have been exhausted.

Files
Sample Trans.xlsx (8.79 KB)

Brian Element Tue, 12/23/2025 - 14:38
  • Hi Anthony,

I imported your file and got rid of any empty rows (Employee Number = 0).  I created a virtual numeric field using the following equation:

 @if(STAFF_NUMBER <> @GetPreviousValue("STAFF_NUMBER"), 0,     (@if(STAFF_NUMBER = @GetPreviousValue("STAFF_NUMBER")  .AND.    RUNNING_NUMBER - 1 = @GetPreviousValue("RUNNING_NUMBER"), 0, @if(STAFF_NUMBER = @GetPreviousValue("STAFF_NUMBER") .AND. RUNNING_NUMBER = 1  .AND. @GetPreviousValue("RUNNING_NUMBER") = 10000, 0, 1))))

It will return a 0 if there is a gab between the previous record and the current record.  You could then create another virtual field to get the previous running number and calculate how many items are missing.

Here is the logic to the equation:

  • If the staff number are different between records return 0 - assumes that we are now looking at a new staff member.
  • If the staff numbers are the same check to see if the previous running number is one less than the current number, if it is then return 0.
  • If the staff number is the same and the running number is one check to make sure the previous running is 10,000, if so return 0.
  • Else return 1 as there is a gab.

Let me know if I missed any scenarios.

Brian