https://netsuiteprofessionals.com logo
Join Slack
Powered by
I am having an issue with date fields in Suite Scr...
# suitescript
s
I am having an issue with date fields in Suite Script 2.0 searches. The goal is: I do a Scheduled Script Instance search to find the last time the current script ran. This time is returned as a string in Pacific Time, which I understand is expected because it is a server side script. I then want to use this Datetime to find all Fulfillment Request transactions that were last modified since that date/time. The time stored on the transaction seems to be the instance's local timezone. It seems to be an actual date, not a string. The script has to be generic to any timezone so I cannot just add the offset and use that time. I tried to create a new date from the Pacific Time string, and use that, but it is now finding 0 new transactions (although some definitely exist in the time frame) and I'm thinking the new Date is being used in UTC, which is then in the "future" for the local time zone? How can I handle this so that both of my searches are using the same timezone (whichever works)?
b
probably want to share your code, but in general the answer is use N/format
s
I tried to use N/format but perhaps im misusing it
is there a way to use it to generalize changing a time from the server time (pacific) to local instance time? I'm not sure how I'd find what the local time is
b
make a distinction between a Date and a datetime string
a Date represents the number of milliseconds since the unix epoch, that moment can be represented in different timezones as different strings
each different timezone will be represented as a different string, but will be the same Date
s
so the search for the last script run time returned just a date string, "11/30/2020 11:21 am" with no time zone information (but we know it is pacific due to it being server side). I used 
new Date("11/30/2020 11:21 am")
to turn it into a Date object with timezone information and tried to use that in my next search for transactions created after then
However this seems not to have worked, although turning it into a Date object did assume pacific time correctly (since the script is server side right) which I verified using 
toISOString
which returned 
2020-11-30T19:21:00.000Z
but using this actual date with timezone information still doesnt seem to be working when I feed it into the transaction search
s
if the script is running serverside, then when it retrieves data from records, that information will also be the same (server) timezone, not the local timezone of whenever the record was made.
s
this does not seem to be the case, since it was finding all of the documents last modified in the last 3 hours (local time is EST so +3 from pacific)
b
most of the time, the date time strings you get from searches will be in the timezone of the current user
s
^
s
yeah so basically that was happening but only on one of my searches. So the first search "last run time" was returning pacific time but the second search was searching using local time
b
Use User.getPreference(options) to get the TIMEZONE preference to see what timezone you are working with use User.getPreference(options) to get the
s
so if I last ran the script at 1:30pm EST and then had 2 transactions, edited at 1pm EST and 1:35EST, it is desired to only find the 1:35 EST document. However, the search for my script runtime returns 10:30AM PST (as a string) and if I fed that directly into the second search for transactions, it used local time and found both transactions, since both occurred after 10:30AM EST
b
for serverside scripts, Date will default to the server's timezone
that has little to do with the string you receive from a search result until you change the search result string into a Date
s
I did turn it into a date though, using the code I sent above, and then it finds 0 transactions for some reason
b
again, you need to get much better at describing your code
or share the actual code
s
what would be helpful to share exactly
the searches?
s
what exactly you are doing with the dates, how you get them
b
share what would be needed for someone to recreate your problem
s
ok, current code (which changes the search result into a date, and which results in the script finding 0 transactions)
Copy code
/**
         * Find the last time the script ran
         *
         * @returns {Date} last run time
         */
        function findLastScriptExecution() {
            var res = search.create({
                type: search.Type.SCHEDULED_SCRIPT_INSTANCE,
                columns: [
                    search.createColumn({
                       name: 'startdate',
                       summary: 'MAX'
                    })
                 ],
                filters: [
                    ['scriptdeployment.scriptid','is','CUSTOMDEPLOY_MYDEPLOYMENTNAME'],
                    'AND',
                    ['mapreducestage','anyof','GET_INPUT'],
                    'AND',
                    ['status','anyof','COMPLETE']
                 ]
            }).run().getRange({ start: 0, end: 1 });
            if (res.length > 0) {
                var scriptDateString = res[0].getValue({ name: 'startdate', summary: 'MAX' });
                return new Date(scriptDateString);
            }
            return null;
        }
this is then fed into this search
Copy code
var lastRun = findLastScriptExecution();
            log.debug({title: 'lastrun', details: lastRun})

            var filters = [
                {
                    name: 'recordtype',
                    operator: <http://search.Operator.IS|search.Operator.IS>,
                    values: [record.Type.FULFILLMENT_REQUEST]
                },
                {
                    name: 'mainline',
                    operator: <http://search.Operator.IS|search.Operator.IS>,
                    values: ['T']
                },
                {
                    name: 'lastmodifieddate',
                    operator: search.Operator.AFTER,
                    values: [lastRun]
                }
            ];
            return search.create({
                type: search.Type.TRANSACTION,
                columns: [
                    { name: 'internalid' }
                ],
                filters: filters
            });
b
Copy code
var scriptDateString = res[0].getValue({ name: "startdate", summary: "MAX" });
return new Date(scriptDateString);
The new Date constructor assumes that the date time string you pass in matches the javascript datetime string format and is in the pacific timezone
s
without the final line of 
findLastScriptExecution
, which is 
return new Date(scriptDateString);
it finds all documents modified in the last 3 hours (the offset of EST with PST)
b
the value from the search result column is unlikely to do that
s
unlikely to do what?
s
to match the javascript datetime string format and be in the pacific timezone
s
Sorry I dont think I'm following what that means
s
Personally rather than working with all this wonky date stuff, I would probably re-engineer the thought process. What is it you want this script to be doing (why do you care the last time it ran)
b
The Date constructor uses a specific format which NetSuite search columns dont normally output
even if they did, it would only work if your user uses the Pacific timezone
s
so what is the solution then?
To answer Sandii, the reason we are doing it this way is because the final search results (eventually) should be all cancelled FRs. Once cancelled, they cannot be edited, so we struggled to find a way to know which cancelled FRs had been exported yet and which needed to be exported. The solution is, export any newly cancelled FRs since the last time the script ran, by looking at the FR last modified date
b
usually the answer is N/format
s
i attempted it but it had no effect (still returned all edited in the last 3 hours before the last time the script ran) but maybe I misused it
b
but one of the acceptable inputs to a date time search filter is a netsuite datetime string
your search column is a netsuite datetime string
s
Im not sure what that means 😕
b
directly use the string from the search column in your filter
s
If I'm understanding correctly though, that is the first thing i tried
b
you dont need to convert it back and forth to a Date, its already in the correct format
s
the simplest way, which was taking the result of the first search and directly inputting it into the second search without any modification at all
except the first search seemed to return a PST datetime string and the second search seemed to be using EST
since that resulted in finding all FRs that had been edited within 3 hours of the script last being run (rather than being edited AFTER the script was last run)
its the inconsistency that is causing this issue with the searches
b
if you happen to be using a search column thats always in pacific timezone, then use format.parse while specifying the timezone parameter
s
so if i understand correctly, I should try replacing 
return new Date(scriptDateString);
in 
findLastScriptExecution
with 
format.parse({value: scriptDateString, type: format.Type.DATETIME, timezone: 5})
?
b
you can print the date afterwards to make sure its the value you want
s
that parse statement should turn it into EST?
b
the parse will output a Date
date are milliseconds since the epoch, which is the same regardless of the timezone
the string representations are the things with timezones
by default, all Dates will use pacific timezone
s
did not change anything sorry
says the last time i ran the script (a few mins ago) after using format.parse is 12/1/2020 7:57 am but its supposed to be 10:57 in EST
didnt change it at all
still finds all docs changed in the past 3 hrs
b
how are you printing it
s
Copy code
var lastRun = findLastScriptExecution();
            log.debug({title: 'lastrun', details: lastRun})
just printing the variable
but regardless of printing its also proving that its not working by finding too many documents
b
lastRun should be an iso8601 string if printed like that
12/1/2020 7:57 am doesnt look like an iso8601 string
s
well i didnt do toISOString on it
b
netsuite does it for you
s
its printing the result of 
format.parse({value: scriptDateString, type: format.Type.DATETIME, timezone: 5})
b
the output of
Copy code
log.debug('scriptDate', format.parse({value: scriptDateString, type: format.Type.DATETIME, timezone: 5}))
should be a string that looks like "2020-12-01T155700.000Z"
s
idk you can see my code 😕
b
if the output of format.parse is a string that matches the input value, then the parsing failed
s
do you see anything wrong with my parse then beause i tried to use the docs
seems everything thats supposed to work for some reason is not 😕
b
what type of script is running this
s
map reduce
b
what are the date format, and time format of the companies general preferences
and what is the timezone in company information
s
message has been deleted
timezone is EST
GMT -5
s
manually running the MR should use the user who fired the MR, not the company preferences (not sure if that matters, I stopped tracking this one)
s
mine is EST as well tho
like idk this is going in circles 😕
b
Copy code
/**
 * @NApiVersion 2.0
 * @NScriptType MapReduceScript
 */
define(["N/search", "N/format", "N/runtime"], function (
  search,
  format,
  runtime
) {
  function getInputData() {
    var user = runtime.getCurrentUser();
    log.debug("TIMEZONE", user.getPreference({ name: "TIMEZONE" }));
    log.debug("DATEFORMAT", user.getPreference({ name: "DATEFORMAT" }));
    log.debug("TIMEFORMAT", user.getPreference({ name: "TIMEFORMAT" }));

    var results = search
      .create({
        type: search.Type.SCHEDULED_SCRIPT_INSTANCE,
        columns: [
          search.createColumn({
            name: "startdate",
            summary: "MAX",
          }),
        ],
        filters: [],
      })
      .run()
      .getRange({ start: 0, end: 1 });
    var scriptDateString = results[0].getValue({
      name: "startdate",
      summary: "MAX",
    });
    log.debug("scriptDateString", scriptDateString);

    var scriptDate = format.parse({
      value: scriptDateString,
      type: format.Type.DATETIME,
      timezone: 5,
    });
    log.debug({ title: "scriptDate", details: scriptDate });

    return [];
  }

  function map(context) {
    log.debug("map context", context);
  }

  function reduce(context) {
    log.debug("reduce context", context);
  }

  function summarize(summary) {
    log.debug("summary", summary);

    if (summary.inputSummary.error) {
      log.error({
        title: "Input Error",
        details: summary.inputSummary.error,
      });
    }

    summary.mapSummary.errors
      .iterator()
      .each(function (key, error, executionNumber) {
        log.error({
          title:
            "Map error on key: " +
            key +
            ", executionNumber: " +
            executionNumber,
          details: error,
        });
        return true;
      });

    summary.reduceSummary.errors
      .iterator()
      .each(function (key, error, executionNumber) {
        log.error({
          title:
            "Reduce error on key: " +
            key +
            ", executionNumber: " +
            executionNumber,
          details: error,
        });
        return true;
      });

    summary.output.iterator().each(function (key, value) {
      log.debug("Output for key " + key, value);
      return true;
    });
  }

  return {
    getInputData: getInputData,
    map: map,
    //reduce: reduce,
    summarize: summarize,
  };
});
has the log output of
so looks like it works for me
@Sandii you can see that my user's date time settings dont match the company's from the logs
the system user uses the company values
s
Yeah I expected the user to be the user who fired the MR manually if not running from a schedule
b
scheduled scripts used to do that, and make things extra confusing
that no longer happens
s
im a little confused what im seeing in the screenshot with which timezones
b
the timezome of my user doesnt really matter
its what the script uses that matter
in this case America/New York
you can infer that my user is Mountain time from the time offset
s
but its important for telling if it "worked"
how can you tell it worked
b
the search column string value is in the scriptDateString
its matching parse value is in scriptDate
importantly scriptDate is an iso8601 string
and more importantly its the matching iso8601 string
s
well I copy pasted your parse code just to make sure i had no typos and while it doesnt work, it seems to do the identical thing as new Date() so idk
Copy code
/**
         * Find the last time the script ran
         *
         * @returns {Date} last run time
         */
        function findLastScriptExecution() {
            var res = search.create({
                type: search.Type.SCHEDULED_SCRIPT_INSTANCE,
                columns: [
                    search.createColumn({
                       name: 'startdate',
                       summary: 'MAX'
                    })
                 ],
                filters: [
                    ['scriptdeployment.scriptid','is','CUSTOMDEPLOY_MYDEPLOYMENT'],
                    'AND',
                    ['mapreducestage','anyof','GET_INPUT'],
                    'AND',
                    ['status','anyof','COMPLETE']
                 ]
            }).run().getRange({ start: 0, end: 1 });
            if (res.length > 0) {
                var scriptDateString = res[0].getValue({ name: 'startdate', summary: 'MAX' });
                log.debug({title: 'scriptDateString', details: scriptDateString})
                log.debug({title: 'new Date', details: new Date(scriptDateString)});
                var scriptDate = format.parse({
                    value: scriptDateString,
                    type: format.Type.DATETIME,
                    timezone: 5,
                  });
                  log.debug({ title: "scriptDate", details: scriptDate });
                  return scriptDate;
            }
            return null;
        }
message has been deleted
just to recap, when I use that actual Date object, that shows up as the iso when i debug it, it no longer finds any fulfillment requests
so i think we're focusing on the wrong issue, since the output of new Date and your output of format parse seem to be equivalent, although i really dont know why parse doesnt work in my environment
b
use 2.0
s
omg is this broken in 2.1
b
my recommendation is to always use 2.1 if you dont know how to do it in 2.0
s
ok but even if format.parse is broken in 2.1, which would be dumb, new Date() is giving me the proper date
yeah this is a brand new script so i made it 2.1 since i thought they want scripts to start being upgraded
i actually forgot about that until you said use 2.0
b
manually run your fulfillment request search
and return the results
you shouldnt be able to use a Date in your filters
s
youre right, that is a bit ominous... but it does seem to be filtering on a date, since when i run my script, it doesnt just return all FRs ever
b
if you can figure out which Date its filtering on, you can try manipulating the date you send
or you can actually run the search and learn what you need to do to a Date to get it into a date filter
s
yeah since i couldnt do it in the UI I'm basing it off of which instances of the FRs the script is finding
Thats how I came to the conclusion that it's using EST
6 Views
Open in Slack
PreviousNext
Powered by