https://netsuiteprofessionals.com logo
Join Slack
Powered by
one more on search filters: i see a number of "uno...
# suitescript
k
one more on search filters: i see a number of "unofficial" lists (blogs, gists) of transaction status codes to filter by - is there an official reference? is this one of those things where it's configurable in the ui and depends on the data? (but it seems by the values to be somewhat standardized?)
e
Nope (that I know of). Of all the glaring omissions, this is one of the wildest omissions of the official documentation IMO
k
no kidding 😮
and even getValue doesn't return 
SalesOrd:A
etc
gonna see if i can find anything in the source code
lol
e
At the bare minimum we should have an official document that lists the correct value in all the applicable contexts (record fields, search results, search filters, etc). It's also something that should have an enumeration somewhere in the SuiteScript API.
k
yeah... welp.
the formula filter trick did not work for either the value (
pendingFulfillment
) or 
SalesOrd:B
, rip
e
Just try 
B
k
oh!
of course, that makes sense
e
I don't know if that will work, but sometimes it's just the letter code
k
["formulanumeric: CASE WHEN {statusref} IN ('B') THEN 1 ELSE 0 END","equalto",1]
results: 0 still
e
The trick I typically use is to run the search with the value I need as a Column just to see what's there (i.e. add a 
formulanumeric
column of 
{statusref}
to see what it holds)
k
this is just a nice-to-have, and actually i guess i don't even need this trick in this case since the data is static / not dynamic
yeah, that's what i tried first
✅ 1
Copy code
{
  "name": "statusref",
  "index": "6",
  "value": "pendingFulfillment",
  "text": "Pending Fulfillment"
}
(from the json response of the api call in a browser)
the distinction between status and statusref is unclear, though statusref appears to be what the ui generates
they both return the same data
i'll move on to seeing if i can just make it a "normal" filter i think
j
From Tim Dietrich's SuiteQL Query Tool:
Copy code
SELECT DISTINCT
	Transaction.Type AS TransactionType,
	Status,
	BUILTIN.DF( Status ) AS DFStatus,
	<http://BUILTIN.CF|BUILTIN.CF>( Status ) AS CFStatus
FROM
	Transaction
ORDER BY
	TransactionType,
	Status
👍 1
k
ah right, this is a select - so i can use 
anyof
anyway \o/
i'll have to figure out how to run that later, but that looks as authoritative as i'm gonna get
j
Can also get to it in an Analytics Workbook on a Transaction dataset.
e
You can deploy this in your account to easily run SuiteQL: https://timdietrich.me/netsuite-suitescripts/suiteql-query-tool/
It also happens to be a great page for running SuiteScript in the console.
e
k
hah, i worked out how to do it in chrome devtools while i was idle in a meeting
but both these links are helpful - thanks
e
I am very bad about searching SuiteAnswers; it's just not a reference I use/rely on
k
in conjunction with "status", "anyof", these values do successfully filter the results
✅ 1
the docs are so hard to navigate i just use google
and i don't know that google indexes suiteanswers
(related nit: .iterator() is not actually a js iterator 😭)
e
^ likely because it is a SuiteScript 2.0 feature, and SS2.0 runs on ES5.1
k
here's a little code you can run in a console where 
require
is valid in a browser that will output an object map of transaction status -> descriptor, according to the suiteql above:
Copy code
require(['N/query'], query => {
    var types = [];
    query.runSuiteQL({
      query: 'SELECT DISTINCT Transaction.Type AS TransactionType, Status, BUILTIN.DF( Status ) AS DFStatus, <http://BUILTIN.CF|BUILTIN.CF>( Status ) AS CFStatus FROM Transaction ORDER BY TransactionType, Status'
    })
        .iterator()
        .each(result => {
            types.push(result.value.asMap());
            return true;
        });
    types.sort((a, b) => a.cfstatus < b.cfstatus ? -1 : a.cfstatus > b.cfstatus ? 1 : 0);
    var obj = {};
    types.forEach(({transactiontype, status, dfstatus, cfstatus}) => {
        obj[cfstatus] = dfstatus;
    });
    console.log(JSON.stringify(obj));
});
could also invert the mapping depending on what you're after. me, i'm just gonna dump it in a typescript file with the source code in a comment so i can regenerate it if i have a need to
e
I know this is "throwaway" code, but for future I think you could use 
asMappedResults()
instead of `iterator()`+`asMap()`
k
Copy code
require(['N/query'], query => {
    const types = query.runSuiteQL({
        query: 'SELECT DISTINCT Transaction.Type AS TransactionType, Status, BUILTIN.DF( Status ) AS DFStatus, BUILTIN.CF( Status ) AS CFStatus FROM Transaction ORDER BY TransactionType, Status'
    }).asMappedResults().map(vals => {
        const ttype = vals.dfstatus.split(':')[0].replace(/[^a-zA-Z]/g, '');
        const titem = vals.dfstatus.split(':')[1].replace(/[^a-zA-Z]/g, '');
        return {ttype, titem, ...vals};
    });
    types.sort((a, b) =>
        a.ttype < b.ttype ? -1 :
        a.ttype > b.ttype ? 1 :
        a.cfstatus < b.cfstatus ? -1 :
        a.cfstatus > b.cfstatus ? 1 :
        0
    );
    const obj = {};
    types.forEach(({ttype, titem, cfstatus}) => {
        obj[ttype] = obj[ttype] ?? {};
        obj[ttype][titem] = cfstatus;
    });
    console.log(JSON.stringify(obj));
});
that gets the job done and produces a more useful format (for typescript)
i suppose i should adjust the sorting now that i've changed what i'm doing, but you get the picture anyway
✅ 1
6 Views
Open in Slack
PreviousNext
Powered by