Showing posts with label PowerApps. Show all posts
Showing posts with label PowerApps. Show all posts

Thursday, November 28, 2024

PowerFx Formatting If Statement Idiosyncrasies

Disclaimer: This entire blog is an opinion, and opinions are like butts, we all have one.  This is not doctrinal truth that must be observed or else you “are dead to me”.  It is just my opinion which I offer with my reasoning in the hopes that it will convince others to make their low-code developer lives and the lives of those that come after them, better. (If you do disagree with, I would love to hear your counter arguments in the comments)

Auto Formatting If Statements Sucks Has Room For Improvement

This is how a simple if statement is auto-formatted within the Canvas Apps:



This is better than a single line, but still sucks has room for improvement.

  1. "If(" does not require it’s own line to distinguish itself from the surrounding code.  The indentation already serves that purpose and vertical spacing in large PowerFx functions is at a premium (but yet it’s premium-ness shouldn’t override readability, more on that later).
  2. The parts of the if are not clearly distinguished as it contains a single uninterrupted block of text .  This is an extremely simple "if" statement, but it takes a lot of attention to visual detail (aka brain power/cognitive load) to separate the condition portion, the true-statement, and the false/else-statement(s).  This leads to bugs and increases the time required to understand what the code is, what it is doing, and what changes may need to be made.

By including the condition on the same line as the if, and most importantly SEPERATING ELSE/ELSE-IF’S WITH AN UNINDENTED COMMA, each part of the if statement becomes clearly defined, and it takes up no less vertical space than before:



This becomes even more important on longer if statements, or if statements with an else/if:



Although vertical spacing is at a premium in PowerFx (all programing languages really), it should always be subservient to readability.  For longer more complicated if statements, readability should be win out, and the parts of the condition can be split among multiple lines, but double indented to clearly define where the true-statement starts:



The choosing of when to split up the if-condition among multiple lines vs keeping it on a single line is much more personal preference than anything else, and is one that may personally fluctuate from time to time (or at least it does for me).

One final thing to note, eliminating an if statement usually results in cleaner code.  In cases where there is just an if statement used to set a Boolean value, removing it is simple and should be done in almost all situations, just like this one:



Happy coding formatting!

Thursday, January 5, 2023

How to Filter Dates in Canvas Apps Using Greater Than/Less Than Operators

Defining the Problem

Recently I was attempting to filter an on-premise SQL table by a DateTime field using a “greater than” operator, and displaying the results in a Data Table control.  When I applied the “greater than” condition to my filter, it would return 0 results.  The crazy thing was I wasn’t seeing any errors.  So I then turned on the Monitor tool and took a look at the response of the getRows request:

{
  "duration": 1130.2,
  "size": 494,
  "status": 400,
  "headers": {
    "Cache-Control": "no-cache,no-store",
    "Content-Length": 494,
    "Content-Type": "application/json",
    "Date": "Thu, 05 Jan 2023 13:36:12 GMT",
    "expires": -1,
    "pragma": "no-cache",
    "strict-transport-security": "max-age=31536000; includeSubDomains",
    "timing-allow-origin": "*",
    "x-content-type-options": "nosniff",
    "x-frame-options": "DENY",
    "x-ms-apihub-cached-response": true,
    "x-ms-apihub-obo": false,
    "x-ms-connection-gateway-object-id": "c29ec50d-0050-4470-ac93-339c4b208626",
    "x-ms-request-id": "e127bd54-0038-4c46-9a31-ce94547c226c",
    "x-ms-user-agent": "PowerApps/3.22122.15 (Web AuthoringTool; AppName=f3d6b68b-f463-43a2-bb2b-b1ea9bd1a03b)",
    "x-ms-client-request-id": "e127bd54-0038-4c46-9a31-ce94547c226c"
  },
  "body": {
    "status": 400,
    "message": "We cannot apply operator < to types DateTimeZone and DateTime.\r\n     inner exception: We cannot apply operator < to types DateTimeZone and DateTime.\r\nclientRequestId: e127bd54-0038-4c46-9a31-ce94547c226c",
    "error": {
      "message": "We cannot apply operator < to types DateTimeZone and DateTime.\r\n     inner exception: We cannot apply operator < to types DateTimeZone and DateTime."
    },
    "source": "sql-eus.azconn-eus-002.p.azurewebsites.net"
  },
  "responseType": "text"
}

Ah, Power Apps shows no error since it returned a 400 status, but the body contains the actual error: "We cannot apply operator < to types DateTimeZone and DateTime.\r\n     inner exception: We cannot apply operator < to types DateTimeZone and DateTime.\r\nclientRequestId: e927bd54-0038-4c46-9a31-ce94547c226c".  Apparently my DateTime column in SQL does not play well with Power App’s Date Time.  After some googling I found some community posts as well:


The Solution

The last community post above suggests that I should try the DateTimeOffset column type in SQL, and after another return to the googling I found a very similar issue described by Tim Leung, describing the same thing.  Unfortunately no one documented how to do this, so here I am, documenting how to do it for you dear reader, as well as future me !  Please be warned, I’m still not sure how DateTimeOffset plays with other tools/systems, so test first!)

  1. Update the DateTime Column in SQL Server
  2. ALTER TABLE dbo.<YourTableName>
    ALTER COLUMN <YourDateColumn> datetimeoffset(0) NOT NULL;

    UPDATE dbo.<YourTableName>
    SET <YourDateColumn> = CONVERT(datetime, <YourDateColumn>) AT TIME ZONE <YourTimeZone>;

    /*
    I don't believe there is a Daylight Saving Time option to timezones, but I just happened to be in EST, not EDT, so my last line looked like this:

        SET <YourDateColumn> = CONVERT(datetime, <YourDateColumn>) AT TIME ZONE 'Eastern Standard Time';

    Use SELECT * FROM Sys.time_zone_info to find your time zone.
    */

  3. Refresh the Data source in the app

  4. In Canvas Apps Studio, click data source options menu and select Refresh
  5. Reload the app
  6. I had problems with the Data Table control I was using not applying the timezone offset correctly.  Reloading the app seemed to fix this issue.

  7. Viola!


It’s not hard, but it definitely is a headache that I would hope Microsoft will solve.