How can I pass information into a Transform Map ?

[Chris L5]

I am at a loss for how to provide a Primary Key for a Transform Map.. that is running on behalf of an Ajax Call.

If I can pass in the ServiceRequestId, and even the ServiceRequestItemId with the Ajax Call.. how can I get the Transform Map to have this information ?  (I don't have a Workflow Context because it's Ajax.)  

Tried looking through 12 different articles in the Dev Forums.  If there is an answer there.. I must be failing to recognize the significance.

My original solution [which didn't need to use Ajax] had the benefit of accessing the "current.sys_id" from the TransformMap Scripts.  Everything was running within a Workflow Context..

With access to 'current', the Transform Script could establish a Primary Key in the Target Table.. which provides a unique id for all rows inserted on behalf of the originating Service Request.

Now, I have been required to remove my logic from a workflow... and instead.. make it work from an Ajax Call.  The Transform Map immediately breaks because 'current' is no longer valid.  

I am assuming this is because the Script Include for handling the Ajax Request is no longer associated with a Workflow Context. I am able to pass in information (Service Request ID and even the Service Request Item's Id) with the Ajax Call.  

If I only have the DataSource, the Transform Map, the ImportSet and the information that I pass in via the Ajax Call.. how can I establish a unique value (ideally the ServiceRequest Item's ID) in a column in the Target Table ?   I can't figure out how to get the information available from my Ajax Request... accessible / visible to the Scripts in the Transform Map.

The file represented by the DataSource is not guaranteed to have a unique name or content..

So I don't see how I can communicate something uniquely identifying via an intermediate table (saved prior to making the Ajax Call).

[ggg]

you state that your issue is:

---

I am at a loss for how to provide a Primary Key for a Transform Map.. that is running on behalf of an Ajax Call.

What is the context? are you importing records from a spreadsheet?

are you running a workflow on a record (if so, what table) and have a transform called from a workflow ( i didn't know you could do this.)

[Chris L5]

The ServiceRequest includes an Excel File attachment. From that ServiceRequest.. a ServiceRequestItem is created and associated with the Excel File Attachment.  The workflow for handling the ServiceRequest.. has a Catalog Task Activity...  the Task is assigned to an appropriate Person.  The Task Reviewer sees their assigned Task and opens up the Task.  They are now on a Catalog Task Form.

On that Catalog Task Form.. we need a button to asynchronously get the Excel file Extract/Transform/Loaded into a custom table and then any issues encountered will be displayed back on the Catalog Task Form.

This allows the Task Reviewer to locate, and modify the Excel file (on the Service Request Item).  The Task Review cleans up any data issues that were detected and then resubmit the modified Excel file (as a new attachment) for another round of Extract/Transform/Load.  

The main issue is that the Customer wants the ability to abort waiting for the ETL and then just proceed thru their Workflow.  They do this now without the ETL capability.. They just do things manually.

If we could force them to wait in a synchronous fashion.. with the Workflow.. I had this already solved.  They don't want to risk the Workflow taking too long .. so they want the ability to skip the ETL and just do things manually.

Thats why the Ajax idea was introduced.. to keep them from getting their UI blocked waiting on a ETL Script that might take too long.  

Not able to make the ETL happen faster.  So have to find an asych solution that allows them to abort waiting for ETL.  

Thats how got into this pickle in the first place 😞

---------------

Maybe I am looking at things from the wrong side of the coin.

Could the Transform Map communicate information back to the calling Script Include ? I need a way for a Query to be run (after the ETL is completed).. which can uniquely identify the records that were just created.   Can't rely merely on the file name of the attachment.   Can't rely on the content within the file attachment... Can't rely only on a timestamp.   Many users could be making simultaneous Service Requests.   So not sure how a Query can find these specific records that appear in the Target table if I am not allowed to use the 'current' variable.

----------------

[Chris L5 · Accepted solution]

Found "a solution"..   not sure if it is the best solution..  but it should be 'good enough' for my purposes.

Simple Answer:

In the "onStart" Transform Script... 

Apparently "import_set.number" returns a value representing the unique ID for the Import Set that is being used by the Transform Map.

Since my core issue is getting a unique id that can be generated before running the Transform Map.. AND.. also accessed during the Transform Map..  AND.. also known after the Transform Map.. this value solves it.

Why ?  Because my Script Include builds the DataSource, the ImportSet object etc etc.. before calling the Transform.  So I know the Import Set's uniqueID before the Transform Map..  and I still have it after the Transform Map.   

I just didn't know / realize the significance of the fact that "import_set" was accessible as a variable from within one (or more) of the Transform Map Scripts.  🙂

Details behind the Answer:

The main core of my problem was insuring that I have a unique value suitable for use as the 'Primary Key' for my 'Target' table in the Transform Map.  This is because after the Transform is complete.. I need to query that Target table for the rows that were just inserted.  But to do that follow-up query.. I need a way to accurately identify the records that belong specifically to the current Service Request that started this whole thing.

In the Transform Map.. my original code (within the context of a workflow.. courtesy of a Script Activity that did all the work)... I could have my Transform Script make use of the implicit 'current' pointer/var/whatever you want to call it.   That allows current.sys_id to have a unique value .. in this case relative to the Service Request that the workflow is processing.  The workflow in this case was bound to the sc_request table.. so the 'current' pointer was relative to the Service Request.  

So in the Transform Map.. I could have my custom target table have a custom string field that stores the value of  'current.sys_id'.   Yes.. technically I would imagine that a Reference field might be more efficient.. but I see to many confusing articles (imho) regarding the need to be really careful with References and Transforms.. so I err'd on the side of caution and just stored the sys_id as a String.

[Chris L5]

#FACEPALM 

Super short story...

So basically.. I wanted a distinguishing value that would allow me to easily find my records after they were inserted. 

The table that I created to receive the results of the ETL (extract/transform/load)..

Must extend the "Import Set Row" table..

So along with that extension comes a column called "Import Set".

#facepalm

That is a unique value shared between all the rows that I just imported with my Script.  I had an answer right there.. and just didn't realize it.  😛