Lookup Datatable 1

Help Orchestrator
,
1

I want to see invoice no from 1 file and billing document from one and match both need to extract Some number and date from my 1 file and write to the 2 file if i get same invoice no
How i can do this?

2

Hi @Kuldeep_Pandey

  1. Drag and drop an “Excel Application Scope” activity onto the workflow canvas.
  2. In the properties panel of the activity, specify the path of the first file you want to extract data from.
  3. Inside the scope, add a “Read Range” activity and assign the output to a DataTable variable (e.g., dtSource).
  4. Add another “Excel Application Scope” activity.
  5. Set the File property of the new “Excel Application Scope” activity to the path of the second file you want to write the extracted data to.
  6. Inside this new scope, add a “Read Range” activity and assign the output to another DataTable variable (e.g., dtDestination).
  7. Add a “For Each Row” activity and set the DataTable property to dtSource.
  8. Inside the “For Each Row” activity, add an “If” activity.
  9. In the “If” activity, use a condition to check if the invoice number in the current row (row("InvoiceNumber")) exists in dtDestination. You can use the “Filter Data Table” activity to perform this check.
  10. If the invoice number is found, extract the desired data from the current row, such as row("Number") and row("Date").
  11. Use the “Write Cell” or “Write Range” activity to write the extracted data to the corresponding row in dtDestination.
  12. Close the “Excel Application Scope” activities.
  13. Finally, use an “Excel Application Scope” activity to write dtDestination back to the second file, saving the changes.

Hope it helps!!