6 Replies Latest reply on Oct 8, 2012 12:00 PM by Terri

    Indicate if a record is brand new or just updated.

    Terri

      Title

      Indicate if a record is brand new or just updated.

      Post

           Hi,

           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. 
            
           Case 1:
           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.
            
           Case 2:
           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?
            

        • 1. Re: Indicate if a record is brand new or just updated.
          Terri

               I need to add to this a bit. I just realized that there is another case to consider. 

               In all I think I need a field that will indicate which part of the Style_Color has changed.
                
               Total scenarios to consider:

               Data: 123456WHIFH2012
               Import: 123456WHISS2013
                
               This needs to indicate New Season. 
                
               Data: 123456WHIFH2012
               Import: 123456BLUFH2012
                
               This needs to indicate New Color.
                
               Date: None
               Import: 456789WHIFH2012
                
               This needs to indicate New Style
          • 2. Re: Indicate if a record is brand new or just updated.
            philmodjunk

                 I'm not sure I follow the logic of how to tell the difference between when something new is being added and when an existing value is being changed, but maybe both changes are considered "new".

                 There's a syntax error here:

                 Case ( WorkOrderData::Style_Num = Style_Num & WorkOrderData::Color_Code ≠ Color_Code; "New Color";
                            WorkOrderData::Style_Num  = ""; "New Style" )

                 Should be:

                 Case ( WorkOrderData::Style_Num = Style_Num AND WorkOrderData::Color_Code ≠ Color_Code; "New Color";
                            WorkOrderData::Style_Num  = ""; "New Style" )

                 But I don't think even that will work here. As I recall, you are matching records between the two tables by Style and color. If either value has been changed, the imported record simply won't match at all to a record in Work Order Data and thus all the fields in WorkOrderDate will be empty in this calculation.

                 You'd need to match records in a relationship by a common value that does not change, ever, before you can compare the other fields in the record to determine what is the same and what represents a change..

            • 3. Re: Indicate if a record is brand new or just updated.
              Terri
                   I understand what you are saying, and you are right, the Style_Color field is the matching field between the two tables. And that field does not change, ever. The import is set up to update matching records and add remaining records as new based on that field. And that field is set to be unique only. So maybe I'm asking the question incorrectly. Let's try a different approach.
                    
                   Say the record I'm looking at in WorkOrder Import table is style_color: 123456WHIFH2012. I want FM to do a search on table WorkOrder Data and tell me if that style (123456) exists at all. If it does not exist, this imported record is brand new. If that is all I can accomplish I will be happy.
                    
                   But hopefully I can take it a step further. Remember (I'm saying remember because you helped me set up this up from the start so I'm refreshing your memory), I have 62 fields in each table and this layout has those 62 fields next to each other and it highlights which of those fields don't match. This is based on the matching style_color field. I also have a calculated field that says if any of those fields don't match it marks it updated. If the style_color ≠ style_color then it marks it new. 
                    
                   Now imagine this: the fields from the data table are blank. The fields from the import table are populated and all highlight that they don't match (obviously). But if I take that style number 123456 (the first 6 characters of the style_color) and do a search on the WorkOrder Data table I find there 5 records. So technically it does exist. But FM is marking it as new because some part of the other characters of that style_color field don't match. Which part? This may not be possible, and I can settle for just telling me if it exists at all. 
                    
                   Hope that helps.
                    
              • 4. Re: Indicate if a record is brand new or just updated.
                philmodjunk

                     You aren't limited to one relationship between a given pair of tables. You can define as many different relationships as you need, you only need create the needed extra "occurrences" of one of the two tables.

                     You have this relationship:

                     WorkOrderImport-----WorkOrderData

                     By making a new occurrence of "data", we can get:

                     WorkOrderData-------WOrkOrderImport-----<WorkOrderDataByStyle

                     In Manage | Database | relationships, make a new table occurrence of WorkOrderData by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as WorkOrderDataByStyle.

                     We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                     Add it to your relationships like this:

                     WorkOrderImport::StyleNumber = WorkOrderDataByStyle::StyleNumber

                     A portal to WorkOrderDataByStyle can list all records in "data" that have a matching style number. You can include the Color and Season fields in the portal row and use conditional formats to show which match and which differ from the current record in "import".

                • 5. Re: Indicate if a record is brand new or just updated.
                  philmodjunk

                       You could use a portal filter expression, or additional pairs of match fields can be included in the relationship.

                  • 6. Re: Indicate if a record is brand new or just updated.
                    Terri
                         THANK YOU!!