Update Database Records Based on Date Comparison

Situation:

I'm trying to map a txt file to a database. I have made my source, target, schemas and flow.

In both schemas I have a date field.

Now, before making updates, I need to check for each row if the date field in the DB is older or newer than the one in the source. If the source date is newer than the DB one, that row should be updated, else that row remains the same in the DB.

How can I make that comparison in the Data Mapper?

 

Solution:

This example will create target records for each source record where the incoming EmpDOB date is more recent (greater) than the database’s EmpDOB date.

 

Create a ForEach on the Root node of your target DB schema with the source schema’s Record node.

  1. Click on the Root target node
  2. Click on the Properties tab (Vertical “Node” tab should already be selected to make Properties tab visible)
  3. Click mouse cursor in ForEach text area (at this point, ForEach text area should be blank)
  4. Double-click on the source schema’s Record node (this should populate XPath of source Record node as seen below)
  5. Click on Save Properties button

 

Create a Local Variable on the Root node of your target DB schema that does a DBQuery function (DB --> DBQuery) to capture the date in your database for each source record

  1. Click on Root target node
  2. Click on Local Variables tab
  3. Type in Local Variable Name
  4. Put mouse cursor in Local Variable Value text area (at this point, Local Variable Value text area should be blank)
  5. Click on DB --> DBQuery
  6. Fill in DBQuery function
    1. Example: DBQuery {'SELECT EmpDOB FROM employee_benefits WHERE EmpSSN = $Input_TSC_EmployeeBenefits/Root/Record/EmpSSN' ,$varDBinfo ,false }
    2. For help on DBQuery function, please refer to Help > Mapping Functions Help in Data Mapper
  7. Save Local Variable

 

Create a local variable that uses the date-difference function

  1. You should already be in Root’s Local Variables tab
  2. Click on “Paper” icon to Add/Clear Local variable name and value text fields
  3. Type in a new Local Variable Name
  4. Put mouse cursor in Local Variable Value text area
  5. Click on Date --> date-difference
  6. Fill in date-difference function
    1. Example: date-difference($Input_TSC_EmployeeBenefits/Root/Record/EmpDOB,'M/dd/yyyy',$varDBQuery,'M/dd/yyyy')
    2. For any help on date-difference function, please refer to Help --> Mapping Functions Help in Data Mapper
  7. Save Local Variable

The date-difference function will return a result based on:

If First Date >= Second Date, then the result is > = 0 else < 0.

 

For the Record node of your target DB schema, create a mapping rule utilizing the IFF condition

  1. Click on the Record target node
  2. Click on Textual Rules tab (Vertical “Node” tab should already be selected to make Textual Rules tab visible)
  3. Click on Conditional --> IF CONDITIONS --> For Filtering Records
  4. Fill in IFF condition
    1. Example: IFF CONDITION{$varDateComp > 0} VALUE=[true ]
    2. You can double-click on date-difference variable in Parameters window that should be visible under Local Variables --> AncestorVariables to populate in rule or just type in variable name preceded by $
  5. Click on green check mark icon to Apply Mapping

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.