We have been stuck on this for a while. I have a table with 1 column, with 1 row. I want to convert its contents into a parameter. We have tried and tried, and not sure how to do this. I created another module, and pulled the value into a fresh table there, but no sure how to make this a parameter despite a lot of chatgpt and Gemini queries. Ultimately, I want to use the parameter in an SQL query, but I just want to be able to create it first! Thanks to anyone who replies on this and shares their knowledge.
Are you backing into this value after loading in data first? I.e, loading from Excel/CSV/database and then doing transformations to get this value? Or, do you already know what it is and just want to reference it?
If you already know what it is, you can just create the project parameter value by clicking on the parameter icon and setting it there in your module. You should be able to reference it almost anywhere you are creating expressions. Your parameters that are built as expressions can pull in other parameter values as well. Just remember you won't be able to access those parameter values in another module unless you send it along with a call module action or iterate action, whatever you are using.
If you need that parameter value that you have already created in the canvas, you can use the "parameter table" action to pull your parameters into a table in your canvas. Then you can use the "peek" action to pull over whatever you need from your table.
https://help.easymorph.com/doku.php?id=transformations:parametertable
I have not found a way to dynamically do transformations on a table after loading data in and the having that value "pulled up" into a parameter value for the module. But you could do that if you are sending to a different module. Like if you are iterating through a column, each value can be passed as a parameter value to the other module and you could place a filter in the other module to dynamically filter sets.
Another option you may want to look into is the shared memory action which let's you set a key value pair. This acts kind of like a global variable that can be referenced mostly everywhere. The link is below. I haven't personally used this before, so I cannot speak more intelligently on it than this. If you explain your use case a bit more, I am sure someone who knows more than I do can help you out.
https://help.easymorph.com/doku.php?id=transformations:sharedmemory
Thanks so much for looking at this issue!
- First step is to import a google sheet with the values I want in a single column
- The import creates a 1 column table where we have values that are text as they are account numbers 1-10000, 2-10000, etc. I call the column GLDetail
- I then you a calculate function to wrap each expression in each row in a { } using the & and " " to put the parentheses around it.
- I then use concatenate function to merge all those rows in the column into a single row, where all the values are joined, separated by comma.
- The result is a 1 row table in the main module that shows like this: {1-10000}, {2-10000}, ...
- I feel this row value is perfect to inject as a parameter into a custom sql, where it will only lookup account numbers listed in that row result.
- the goal is to have a google sheet that I can add data to, and it automatically updates my query with the additional rows.
- I have tried a lot of things, including Call it into another module, but I am have never been able to build a parameter from the 1 row table it or iterate the the column in the step before concatenate using the account numbers.
- I would need really precise instructions! Thanks
Here is an example of converting a cell from a 1-row table to a parameter:
cell-to-param.morph (3.8 KB)
Let me know if you need me to annotate the actions. Notice that in the "Call" action the parameter is assigned using a first column value:
Thanks again, the screenshots really helped but always hit errors with the SQL or the Call. Here are the issues I think I am having:
If you look at the parameter pAccountList it is not wrapped in () which I think causes and issue when injected into the custom SQL in Module 1
When I wrap '{pAccountList}' in () the SQL query drops the error but no results.
I am able to retrieve data when I manually type in Custom Sql
[AccountNumber] IN (N'8-46000', N'8-47100', N'7-46000')
but I think my parameter is injecting it as
[AccountNumber} in N'8-46000', N'8-47100', N'7-46000'
Again, thanks for looking.
Probably, the way you use for wrapping is incorrect. At least from what I see in the screenshots, you wrapt the whole parameter value (which is multiple constants) in single quotes, which doesn't seem correct. If your SQL query is as in the screenshot
[AccountNumber] IN `{pAccountList}'
And {pAccountList} contains N'8-46000', N'8-47100', N'7-46000'
Then the resulting query will be (notice erroneous quotes):
[AccountNumber} in `N'8-46000', N'8-47100', N'7-46000'`
Instead, you can wrap the parameter in parenthesis in custom SQL:
```
[AccountNumber] IN ({pAccountList})
```
Keep in mind that EasyMorph inserts parameters verbatim in the query text. It's like pasting something from Clipboard.
Also, you can always check the actual SQL query after parameter insertion by clicking this button in the Query Editor:
I finally have a working solution.
I wanted to make this simple for regular human beings who are not coders. Here are the steps to convert a table result to a parameter to use in SQL.
In Main Module (this is the default view any project is open in).
Action 1: I imported a 1 column Google Sheet. You will need to setup a connector where you allow Easymorph to connect to your Google Drive via Manage Connectors, Add Connectors, Google Drive. Authorize. Pick the file you want and the sheet you want and choose Import Action - Import Table and load only the selected columns. For me I have one column with a label in the top row. My column was called GM_PROFIT
Step 2: I did a calculate row fiunction where I wrapped the values in the column in a SQL required characters. In my case, the text in the column read as 1-100000, 2-100000 which I wrapped using this function " N'"&[GMProfit]&"'", which wrapped each row like this N'1-100000'
Step 3: I used Concatenate Text Function, Concatenate was applied to column GM_PROFIT. I checked off distinct Values, and chose comma as separator. This creates one row from all the previous rows. It now looked like N'1-100000, N'2-100000
Step 4: Call Another Module/Project - this sends output to another module as a parameter.
This is where it gets a little tricky. Choose Module to Run - choose Module 1. If you don't have a module 1, add it now. Modules show on the right side. You may have to expand the arrow on the right to see them . For Mode, choose, Do Not Return Result Table. It will then say - Parameter in Module 1. If there is nothing there, you may need to leave this, switch to Module 1 on the right, go to Edit Parameters, and New Parameter, choose Text or Number, give a parameter name i.e. Parameter Module 1 = (leave it blank). I called mine pAccountList2
Go back to main module, Call Another module Project and choose Module 1, Do Not Return Table Result, Parameter in Module 1 - (choose your newly created Parameter (Parameter Module 1) = GM_PROFIT (which in your case is the name of the column in your main module), then under This Project (replace GM_PROFIT with the name of your 1 row table in Main, and hit the drop down arrow outside and to the right of This Project Box to choose First Value of Column.
Now head to Module 1, choose Parameter Table as an action, and mine created one called pAccountList 2 - which is the parameter I created beforehand.
Then, add Data. I added my MS-SQL,(which I already had connected in Manage Connectors), using the visual SQL to see my Database and chose custom SQL to add the following condition to Where: [AccountNumber] IN ({pAccountList2})
The field had to be wrapped in and the parameter in ({ })
This then cycled through all of the accounts from the original Google sheet (so I can now just add to my google sheet to keep the report updated as values change. Tears flowed as the data came in.
Note, when you use custom SQL, your visual sql won't work, so any other filters need to be done in customer SQL.
One very important note, I had done all the steps and it wasn't working. I opened the parameter pAccountList2 in Module 1 in edit mode after running it and having blank values, and it was as if it was still = (empty) like when I created it, so it was not helping in my query. When I opened the parameter in Edit Paramaters to edit it, a popup came saying do you want to overwrite value with the new value and I could see the new value was the 1 row result I wanted, so I said yes. Then everything worked and my query was using the parameter. I have to check if this is updating automatically now (I think so).
So, first table in Main Module looked like this:
Call to Module looked like this:
Module 1 table looked like this:
Module 1 Parameter looked like this - after updating after the Call Module was run, and then opening the parameter to edit and being asked if I wanted to change it from the blank value I originally had.
Thanks for the help dgudkov and Perk
1 Like
Thank you for the detailed step-by-step explanation, @Admin_DealerUp. I'm sure it will be helpful for many.