Workaround for a "Dynamic Join"?

[laurennewton]

Hi all! Here is my predicament... 

I am currently tryin to join two different datasets the goal is to take a running database file I have that compiles users inputted data (smaller amount of records ~100records at most )with an sap pull that brings in additional data(larger data pull ~20K - 40K records). The trouble is I need to join fields based on what the user has chosen which could be any number of different types of fields. I've been searching the discussion boards for possible workarounds but can't seem to find anything. The goal is to take names and whichever matching field the user has inputted and append those names to applicable rows where their matching field data exists. I.E. A user inputted record that has a name of "John Doe" and a matching field labeled "Project Name" with a code of 1234. The goal is to match dynamically somehow on "Project Name" (because i could have another user inputted record that they want to match on "Part Number") and then add that name inputted "John Doe" to all instances of project name 1234 in the sap pull records.

If it would be helpful I can create some dummy data inputs and upload those if my description is not helpful!

[gaoa]

Hi @laurennewton Your question sounds interesting! Would be more helpful if you share some input data and desired output?

[Yoshiro_Fujimori · Accepted solution]

Hi @laurennewton ,

I understand you want to execute a search 'sap pull data' with the criteria described in each 'users inputted data'.

So you need to execute search multiple times. In that case, "Batch Macro" would come in handy.

User Input Data 

'sap pull data'

Main workflow

You need to edit search criteria to feed to Batch Macro.

Batch Macro

reads each search criteria and search the 'sap pull data' and output the matched records with the Query ID.

The Query ID will be used outside of the Macro to add the search information.

Output

Please let me know if this works for your case.

[laurennewton]

@Yoshiro_Fujimori love this idea! i'm attempting to use this in my workflow but i'm getting error messages like this: 

ErrorLink: DynamicJoin (3): Record #15: Tool #2: Parse Error at char(23): Unknown variable "A1BCCDE"

It gives this error for every user record, that specific example I listed is a project name. The macro itself when I run it runs fine with no errors but when I run it in the workflow it gives those errors.

Any idea why it throws this error?

[laurennewton]

@gaoa Yes! I've attached a workflow with text inputs that have dummy data. 

[Yoshiro_Fujimori]

@laurennewton ,

Your data "search criteria" has a column "Criteria" to feed to the macro.

But the string stored in the column is not ready for use in Filter tool.

So you need to correct them.

Also your criteria refers to [Project Definition], while the actual column name on "SAP Data Pull" is "Project Name".

This also caused an error.

I corrected the Criteria and addes Query ID to set the query data.

It seems to work now.

I see "IPT" is set as null in some records on your "Desired Output" while they are set in the actual output.

But I suppose it should be OK.

[laurennewton]

@Yoshiro_Fujimori  thank you! i've got it working