Jon1

Matching records exactly - not all that are similiar

Discussion created by Jon1 on Jun 21, 2018
Latest reply on Jun 22, 2018 by ThierryGuemboura

I have an issue with the wrong records updating.

In the screen shot below, the idea is to enter a roll number (pertains to a roll of paper in inventory) and all the other fields automatically fill in. At the end of the job, the operator inputs the ending diameter number which updates the roll of paper in inventory.

 

The next time the roll of paper is used, the “Beg DIA” (beginning diagram) column should be the same as the “End DIA” from the previous job. This works.

 

The problem is that every roll of paper with a similar roll number is also updated. The roll numbers are generated from the paper mill so we can’t change them.

 

The relationship looks like this.

 

In the “End DIA” field, there is a script trigger that opens a “Consumed” layout, creates a new record with an auto enter date, and copies the roll number and ending diameter to the new record in the Consumed layout. The script looks like this.

 

The consumed layout from the above entry looks like this. You can see there is only 1 record that was created by the script trigger in the “End DIA” column.

 

The Inventory Items layout looks like this with a portal to the consumed table. In the example below the Roll Number in the portal matches the Roll Number on the left – which I want.

 

The problem is that the next few records with a similar roll number are also updated. Which we don’t want.

 

The calculation in the table is as follows.

 

We calculate the weight based on the diameter, so it is important to know that when a roll is first added to inventory the original diameter is needed. That is why in the above calculation, if there is no date in a related consumed record, then the current diameter is the original diameter. If there is a date, then the current dimeter is the ending diameter from the previous job. I use the Min() to get the lowest diameter since a roll can be used more than once.

 

What can I do to make the roll number that matches exactly be updated and other similar roll numbers be untouched?

 

Thank you for any help.

Outcomes