Indicate if a record is brand new or just updated.
I have two tables - relationship is Style_Color = Style_Color.
1. WorkOrder Data
2. WorkOrder Import
The purpose of the first table is to hold the original data. Table two brings in the new import of a CSV each week. I then have a layout that compares the fields from the original table to the fields in the import table and highlights what fields on the import file have changed (or don't match) by conditional formatting. A user then compares the records on that layout and makes necessary changes to the work order if needed. This layout will also indicate if a record is new.
An example of the style_color field is: 123456WHIFH2012.
Broken out it is: Style number (123456) Color Code (WHI), Season (FH), Year (2012). It is a field that conactenates those four fields.
Here is the issue.
On the data table there is a record with the style_color field as: 123456WHIFH2012
When a new import comes in there is a new record with style_color field as: 123456BLUFH2012. This is marked NEW. In this case the only thing that changed is the color. So technically this is a new COLOR for style 123456.
A new import comes in with a new record with style_color field as: 456789WHIFH2012. This style does not exist on the data table. This is also marked NEW. In this case style 456789 is brand new.
My needs: I need a flag on each record that will indicate if it is a new color (Case 1) or a new style number (Case 2).
I currently have a field called Type_New on the WorkOrder Import table and it has a calculation as follows:
Case ( WorkOrderData::Style_Num = Style_Num & WorkOrderData::Color_Code ≠ Color_Code; "New Color"; WorkOrderData::Style_Num = ""; "New Style" )
This only returns New Color - even though I know there are records that are brand new. What am I doing wrong?