Pointing to a Functional Currency Field from USD Amount Field in Direct Connection

RR_OneS_QPO
New Contributor II

What is the best approach to point to a different field in a Direct Connection Data Source. 

I am familiar with importing both local and USD currency (not using translation) by selecting the appropriate columns in a Delimited File, and this has worked fine in the past. I am now trying to do the same in Direct Connection to a Data Source. I tried using the Set and Get Global Variable as shown below, but the results are not as expected. For example I am expecting a USD value of 15482.72 and it is returning 22.61, this value is not even close in the set of data rows returned by the SQL query. Here is the complex expression I have in the Amount Field:

Raul_Rodriguez_0-1680816260713.png

And in the Functional Functional currency field I have the following complex expression:

Raul_Rodriguez_1-1680816382619.png

 

Any help is greatly appreciated.

 

1 ACCEPTED SOLUTION

After a deeper review, I found that the process I followed to get the right value in the amount column using the Global Variables in the connector was actually adequate. The issue turned out to be: The sql view provided by the client was actually returning a transactional currency value in the USD field.

Thank you for your response.

View solution in original post

2 REPLIES 2

JackLacava
Honored Contributor

I think we need a bit more clarity here. Correct me if I'm wrong:

  • You have a Connector you wrote to pull data from some arbitrary source
  • you're running some Transformation rule on it, to manipulate values (?) and that's what is failing
  • I'm not sure what you mean by Direct - Direct Load is a Workflow mode, are you using that to run your Connector?

Generally speaking, if you're in control of Connector code, it might be easier to do any "dirty work" there, presenting a clean and simple table to transformation rules. Grab the source data, then modify the DataTable or create a new one that contains "clean" records. 

There isn't (or rather shouldn't) be any meaningful difference between Connectors or Fixed/Delimited Files, in terms of how Transformation Rules run and reference source data; I believe there might be some differences in how those rules run if you're using Direct Load, but again, if you're using Direct Load you're doing it for speed, so you'd want to keep your transformations simple and fast and do the heavy lifting inside the Connector.

After a deeper review, I found that the process I followed to get the right value in the amount column using the Global Variables in the connector was actually adequate. The issue turned out to be: The sql view provided by the client was actually returning a transactional currency value in the USD field.

Thank you for your response.