Is there a way for N/query's asMappedResults to re...
# suitescripts
screnshaw
07/11/2022, 3:52 PMIs there a way for N/query's asMappedResults to return the json with the label values? I don't want id_1 to be returned. I'd prefer transaction.id or transactionId
s
Shawn Talbert
07/11/2022, 4:58 PMNFT's
nsSearchResult2objShawn Talbert
07/11/2022, 5:02 PMas a best practice, we always use valid JS identifiers as labels on searches created in the UI meant for scripts to use. This way it's easy to link the search columns to the code names without an additional mental mapping.
s
Sandii
07/11/2022, 6:27 PMCould you modify the query columns and read the labels and make them the alias? I believe asMappedResults names them after the alias you have chosen
s
screnshaw
07/12/2022, 6:17 PM@Shawn Talbert sorry for the delay, just read these! what do you mean by the js identifiers?
screnshaw
07/12/2022, 6:18 PM@Sandii I can modify it in the UI and code if it's possible. How would you change the alias?
s
Shawn Talbert
07/12/2022, 6:19 PMI mean making label text be valid JS identifiers then use those in the code.
s
screnshaw
07/12/2022, 6:20 PMI changed the label to be transactionId, but when I run asMappedResults it is coming back with id_1 still
s
Shawn Talbert
07/12/2022, 6:22 PMit's NFT nsSearchResult2obj that obeys the labels
s
Sandii
07/12/2022, 6:22 PMDid you use suiteQL syntax or query.create()? If using the query.create() syntax, you can definte the
aliass
screnshaw
07/12/2022, 6:22 PMI just used the query.load
screnshaw
07/12/2022, 6:22 PMquery.load and then the iterator
s
Sandii
07/12/2022, 6:23 PMyou could try retrieving the
query.columnsaliass
screnshaw
07/12/2022, 6:28 PMOk, I can get the label in the result.data.columns. Is there a way to change the alias in the UI or does it have to be in the code?
s
Sandii
07/12/2022, 6:29 PMI dont know a way to change the alias in the UI, never tried
s
screnshaw
07/12/2022, 6:30 PMHow do you normally set the alias in the n/query? I'm new to analytics, so I was just creating it in the UI. It's pretty simple. I'm getting a few fields from the customer table and a few fields from the transaction table
s
Sandii
07/12/2022, 6:31 PMDepends, its you are using the long annoying syntax with query.create, its just one of the properties you set in the query.createColumn() call. Most people I think just use suiteQL syntax (like writing a long string query). I don't know/never tried to use/set alias with suiteQL synax
s
screnshaw
07/12/2022, 6:39 PMIs there a way to easily convert what I have to suiteql?
s
Sandii
07/12/2022, 6:40 PMQuery.toSuiteQL()Sandii
07/12/2022, 6:41 PM Copy code
Important The resulting SuiteQL query string (contained in the SuiteQL.query property) does not include any aliases you set on query result columns in the original query.Query object. For more information about aliases, see Column.alias.s
Shawn Talbert
07/12/2022, 7:06 PMaliases in the SQL sense do work just fine with suiteQL, no?
Shawn Talbert
07/12/2022, 7:08 PMI mean
select foo as bar from Tbarfoos
Sandii
07/12/2022, 8:05 PMpretty sure that is how it works... I would think loading the query from UI would make the labels like that codewise but I am not sure
s
screnshaw
07/12/2022, 8:43 PMOk cool! I'll test it out!
screnshaw
07/12/2022, 8:46 PMOof, that's rough! lol
SELECT
BUILTIN_RESULT.TYPE_STRING(Customer.entityid) AS entityid /*{entityid#RAW}*/,
BUILTIN_RESULT.TYPE_INTEGER(Customer."ID") AS "ID" /*{id#RAW}*/,
BUILTIN_RESULT.TYPE_INTEGER("TRANSACTION"."ID") AS id_1 /*{entity<transaction.id#RAW}*/,
BUILTIN_RESULT.TYPE_STRING("TRANSACTION".tranid) AS tranid /*{entity<transaction.tranid#RAW}*/,
BUILTIN_RESULT.TYPE_STRING("TRANSACTION".custbody_ad_accounting_hold_reason) AS custbody_ad_accounting_hold_reason /*{entity<transaction.custbody_ad_accounting_hold_reason#RAW}*/,
BUILTIN_RESULT.TYPE_INTEGER(Customer.consoldaysoverduesearch) AS consoldaysoverduesearch /*{consoldaysoverduesearch#RAW}*/,
BUILTIN_RESULT.TYPE_CURRENCY(Customer.creditlimit, BUILTIN.CURRENCY(Customer.creditlimit)) AS creditlimit /*{creditlimit#RAW}*/,
BUILTIN_RESULT.TYPE_CURRENCY(Customer.balancesearch, BUILTIN.CURRENCY(Customer.balancesearch)) AS balancesearch /*{balancesearch#RAW}*/,
BUILTIN_RESULT.TYPE_INTEGER(Customer.custentity_mhi_ad_grace_period) AS custentity_mhi_ad_grace_period /*{custentity_mhi_ad_grace_period#RAW}*/
FROM
Customer,
"TRANSACTION"
WHERE
Customer."ID" = "TRANSACTION".entity(+)
AND (("TRANSACTION"."TYPE" IN ('SalesOrd') AND "TRANSACTION".status IN ('SalesOrd:D', 'SalesOrd:A', 'SalesOrd:F', 'SalesOrd:E', 'SalesOrd:B') AND "TRANSACTION".custbody_mhi_ad_expected_ship_date IS NOT NULL))