Forum Discussion

WilliamHunter's avatar
WilliamHunter
Qrew Trainee
11 months ago

Code help

Okay I am fighting with the syntax of this code more than the logic of it, I BELIEVE, I am trying to increment hours accordingly. What I mean by this is if the store type is a Walmart or a Sam's club and it falls outside of the hours of operation I want to add time to it. I would really prefer to make it the next time they are opened, but I can play with that later. For example a WO came in at 3:05am on 6/14/2023 and due to the priority and a barrier code, irreverent abstraction for this, it adds 18hrs to the time. The time then lands at 7:05pm which is right outside of the range of open hours for Sam's club, therefore it should 14 hrs to the time to make it fall within that range. If that makes any sense, but my code is acting like 7:05pm is within the range. Please advise:

Code:

//Now that the corrected time has been handed off to this field it will check based on if its a SAMs club or a Walmart if the hours fall during closing time. If they do then it will add the corresponding amount of hours to it. 
//Declare the variables so the hours can be changed later if needed
var duration samshrs = Hours(12);
var duration walmarthrs = Hours(8);
var TimeOfDay samsclose = ToTimeOfDay("7:00pm");
var TimeOfDay samsopen = ToTimeOfDay("9:00am");
var TimeOfDay walmartclose = ToTimeOfDay("11:00pm");
var TimeOfDay walmartopen = ToTimeOfDay("6:00am");

//If then statement to increment the hours if they fall within non-working hours.
If([Project Site - Store Type] = "Wholesale (Sam's Club)" and ((ToTimeOfDay([Work Order Request Recieved Date/Time] + [Duration Counter]) >= $samsclose) and (ToTimeOfDay([Work Order Request Recieved Date/Time] + [Duration Counter]) < $samsopen)), ([Work Order Request Recieved Date/Time] + [Duration Counter] + $samshrs),

If([Project Site - Store Type] = "Retail (Walmart)" and ((ToTimeOfDay([Work Order Request Recieved Date/Time] + [Duration Counter]) >= $walmartclose) and (ToTimeOfDay([Work Order Request Recieved Date/Time] + [Duration Counter]) < $walmartopen)), ([Work Order Request Recieved Date/Time] + [Duration Counter] + $walmarthrs),
([Work Order Request Recieved Date/Time] + [Duration Counter])))



------------------------------
William Hunter
------------------------------

5 Replies

Sort By
  • ChristopherWhea's avatar
    ChristopherWhea
    Qrew Cadet
    11 months ago

    Hi William,

    If it were me, I'd likely make a few more variables to improve readability and make it easier to test.  The syntax issue you're having might be the extra If() -- Formulas in QB essentially take the format: If, Then, If, Then...,,Else.  You might have to play around with this a bit, but here's a start:

    var duration samshrs = Hours(12);

    var duration walmarthrs = Hours(8);

    var TimeOfDay samsclose = ToTimeOfDay("7:00pm");

    var TimeOfDay samsopen = ToTimeOfDay("9:00am");

    var TimeOfDay walmartclose = ToTimeOfDay("11:00pm");

    var TimeOfDay walmartopen = ToTimeOfDay("6:00am");

    var TimeOfDay initialTime = ToTimeOfDay([Work Order Request Recieved Date/Time] + [Duration Counter]);

    var bool samsClosed = $initialTime >= $samsclose and $initialTime < $samsopen;

    var bool walmartClosed = $initialTime >= $walmartclose and $intialTime < $walmartopen;

    If(Project Site - Store Type] = "Retail (Walmart)" and $walmartClosed,

         TIME or DATETIME CALC YOU WANT,

    [Project Site - Store Type] = "Wholesale (Sam's Club)",

         TIME or DATETIME CALC YOU WANT

    )

    Do you want the field to resolve to be the $initialTime if the store is open and the next opening time if the store is closed?  Or is the result a Date/Time?



    ------------------------------
    Chris Wheatley
    ------------------------------
    • WilliamHunter's avatar
      WilliamHunter
      Qrew Trainee
      11 months ago

      Okay perfect, the information of the if-then, if-then, else if very valuable. Thank you! I will play around with it a bit. Also, the end result will be a Date/Time result that I will pass into another field to calculation duration. 



      ------------------------------
      William Hunter
      ------------------------------
    • WilliamHunter's avatar
      WilliamHunter
      Qrew Trainee
      11 months ago

      Well now I have come across something very odd. I have done the following code for a sanity check:

      //Declare the variables so the hours can be changed later if needed
      var duration samshrs = Hours(12);
      var duration walmarthrs = Hours(8);
      var TimeOfDay samsclose = ToTimeOfDay("7:00pm");
      var TimeOfDay samsopen = ToTimeOfDay("9:00am");
      var TimeOfDay walmartclose = ToTimeOfDay("11:00pm");
      var TimeOfDay walmartopen = ToTimeOfDay("6:00am");
      var TimeOfDay initialTime = ToTimeOfDay([Work Order Request Received Date/Time Corrected] + [Duration Counter]);
      var bool samsClosed = $initialTime >= $samsclose and $initialTime < $samsopen;
      var bool walmartClosed = $initialTime >= $walmartclose or $initialTime < $walmartopen;

      If($walmartClosed, true, false)

      and I alternatively put, (ToTimeOfDay([Work Order Request Received Date/Time Corrected] + [Duration Counter]) >= $walmartclose) in prior to this, but for some odd reason the system does not view 2:15am as past the closing time of 11pm, perhaps its because its the next day. This caused your clean up to not work. I like the format of the code for readability as you said, but I am unable to use an "and" logic function due to this. How could I resolve this issue? 



      ------------------------------
      William Hunter
      ------------------------------
      • ChristopherWhea's avatar
        ChristopherWhea
        Qrew Cadet
        11 months ago

        Ah, yes.  Since you're only working with TimeOfDay in your formula and not Date/Time, the 'or' that you have in $walmartClosed should do the trick.  This is assuming, however, that you don't have any stores that close after midnight.  If that's the case, your formula will need to be a little more complex but it's still doable.

        var bool samsClosed = $initialTime >= $samsclose or $initialTime < $samsopen;
        var bool walmartClosed = $initialTime >= $walmartclose or $initialTime < $walmartopen;



        ------------------------------
        Chris Wheatley
        ------------------------------