I am trying to use the Selection Node Input to limit and rename the fields I want to export to Excel. However, I can not figure out how to set this up. The documentation is no help. It states to use the a!querySelection() and then the a!queryColumn() functions when setting up the columns. However, the configuration input seems to have the ability to add columns, fields, alias and visible without using the functions.
I have tried to key in the fields, alias and visible into each of these inputs, but for the life of me, I can figure out how to add in more columns and adding just one selection throws an error, "Query has failed: Could not parse expression [1]. Details: Unexpected count of record variables (APNX-1-4505-005)"
I have tried to write the Jason using the a!querySelection(a!queryColumn(),a!queryColumn(),etc...) at the Selection and Columns level. This just throws the same error as above.
a!querySelection( { a!queryColumn( pv!pensionContributors.rowNumber, "Request_Id" ), a!queryColumn(pv!pensionContributors.clergySSN, "SSN"), a!queryColumn( pv!pensionContributors.clergyFullName, "Name" ), a!queryColumn( pv!pensionContributors.clergyStatus, "Clergy_Status" ), a!queryColumn( pv!pensionContributors.clergyGender, "Clergy_Gender" ), a!queryColumn( pv!pensionContributors.clergyDoBirth, "Date of Birth" ), a!queryColumn(pv!pensionContributors.clergyAge, "Age"), a!queryColumn( pv!pensionContributors.clergyDoOrdination, "Ordination Date" ), a!queryColumn( pv!pensionContributors.clergyDoHire, "Hire_Date" ), a!queryColumn( pv!pensionContributors.clergyYearsofService, "Years_of_Service" ), a!queryColumn( pv!pensionContributors.clergyDoParticipation, "Particpation_Date" ), a!queryColumn( pv!pensionContributors.clergyDoRetire, "Normal_Retirement" ), a!queryColumn( pv!pensionContributors.clergyPensionStatus, "Pension_Status" ), a!queryColumn( pv!pensionContributors.pensionStatusDate, "Status Date" ), a!queryColumn( pv!pensionContributors.comment, "Comment" ) } )
Discussion posts and replies are publicly visible
You can try in the following way:
a!querySelection( columns: { a!queryColumn(field: "FirstName"), a!queryColumn(field: "MiddleName", alias: "MName") } )
I'm not sure I'm describing my issue. I am trying to use the Export DSE to Excel smart service in a process model. I would like to limit the rename the fields for the spreadsheet.
When configuring the Export DSE to Excel smart service in the process model on the Data tab, one of the Node Inputs is called Selection. According to the documentation, the purpose of this is "A list of fields to retrieve, created with a!querySelection(). The selection accepts up to 50 columns."
a!querySelection()
When expanding the Selection input, there are several Node Inputs "below" the Selection Node Input in a hierarchical relationship.
If you click on the Selection Node Input, in the Field Properties, In Field Properties it states "Value set via fields. Click or overwrite".
I clicked to overwrite, and used the syntax you provided.
= a!querySelection( columns: { a!queryColumn( pv!pensionContributors.rowNumber, "Request_Id" ), a!queryColumn(pv!pensionContributors.clergySSN, "SSN"), a!queryColumn( pv!pensionContributors.clergyFullName, "Name" ), a!queryColumn( pv!pensionContributors.clergyStatus, "Clergy_Status" ) } )
I saved and published the Process Model and received the following error.
"Query has failed: Could not parse expression [1; 2; 3; 4; 5]. Details: Unexpected count of record variables (APNX-1-4505-005)"
I removed the Json from the Selection Node Input and pasted it into the Columns Node Input and got this error message.
"Either selection or filters are invalid: Cannot execute a query with a blank field name. (APNX-1-4505-009)"
I changed the Json to this:
= a!querySelection( columns: { a!queryColumn( field: pv!pensionContributors.rowNumber, alias: "Request_Id" ), a!queryColumn( field: pv!pensionContributors.clergySSN, alias: "SSN"), a!queryColumn( field: pv!pensionContributors.clergyFullName, alias: "Name" ), a!queryColumn( field: pv!pensionContributors.clergyStatus, alias: "Clergy_Status" ) } )
I received the same results. The basic issue is where and how do I need to configure the fields I want to select and the name that I want to be in the spreadsheet columns?
Chris.Gillespie said:and used the syntax you provided
No you didn't. Here's the context you were provided:
Here's what you did instead:
The syntax expects field names -- actual, plaintext, names. You passed a PV value i.e. "pv!pensionContributors.clergySSN", which is NOT a "field name". Thus, when you try to run the process model with this entered, the process will be attempting to read the value of "pv!pensionContributiors.clergySSN" to determine the field name it contains, which i'm guessing (based on your error message) is currently blank.
Another hint is, a!querySelection() works exactly the same way here as it does when used in a!queryEntity(). I fear you might be getting this confused with how record type properties are referenced when using RecordType data, where you need to reference the record type before naming the property. For entity data, all you need is the field name (which is refreshingly simple in comparison, lol).
Mike, thank you for the quick response, this got me over the issue!!!
I changed the code in the Columns Node Input and received the same error message. I cut and pasted the code into the Selection Node Input and it worked!!
Good
Great, thanks for confirming!