Low-Code Apps?
I love the idea of Power Apps, because we all love Power BI cross-filtered charts and analytics (you do, right?). Power Automate has huge potential too. So yes, we’re pretty much sold on the whole Power Platform thing.
But Power Apps is not as mature and hammered out as Power BI. Which is a shame, because to get data insights out, you need data in. Even if that’s additional data to augment the existing or streamed/IoT data sources. For over 2 decades we’ve used Microsoft Access and VBA for that (it’s quick, powerful and cheap – and still is), then Winforms in C#, then checked out but avoided overkill on WPF and UWP. We checked out Access Web Apps (the worst of all worlds) and LightRoom (potential, but a forerunner) and both of them pretty much got shelved by Microsoft. Because they had Power Apps coming. Maybe we wouldn’t have to go with Qt or other issue-laden app development systems.
So back to the plot – Power Apps with real databases (not SharePoint or Excel files, neither of which are “delegable” ie guaranteed to deliver confident queries with more than 2,000 records without a mass of hassle). After all, we generally deal with millions of records, not just thousands. So after seeing how excellent Power Apps was with running up quick screens on both Android and iPhones (or tablets and even browsers), we did some test apps and rolled out a client one for UAT. So far so good. But it revealed those caveats. Let’s go back to the drawing board with a base app for repro – a stripped down Mini Timesheet App.
Mini Timesheet Test App
Keeping it simple to avoid unnecessary fields, we have:
- TimeID: Integer (Primary Key, AutoIncrement);
- StartDate: DateTime (formatted as simple Date);
- Status: Integer for DropDown Lookup;
- Notes: Text (Multiline in App, nvarchar in SQL Server);
- Hours: Decimal, with a SQL Server Check Constraint < 8.
Overriding record-level Save icons are the top global ones:
- Save Basic: Use ForAll to save any changes on every record in one hit.
- Via Collection: Additionally use internal Collection object to “regulate” the records pre-save.
- Error Context: Hold any backend errors generated in client-side object.
Now we’ll check out their reality…
The fact is, record-level saves are kid’s stuff. Users want to see multiple records on the same page – the old “repeating table” setup. Power Apps does this with a “Gallery”, which works well. Until you want to edit as well as just view them. In this case, folk understandably want to edit a few entries at a time. With record-level save, only the current record is saved, with the remaining ones ignored – even worse, on refresh the non-selected records are effectively wiped clean of any changes! Not what users want, or even expect. Quite frustrating in fact.
Fortunately, Microsoft understand this (they’ve been doing it for quite some time…) so they’ve put forward the “ForAll()” formula command, to take an action on each object within it. Only it doesn’t work quite the way as expected either – you can make it work, but it exposes a whole load of internal workings you may wish you’d never have to scrape away at. And those issues (with resolutions – don’t worry!) will be covered in the next part of this 2-part posting.
For those that want to test this, use the following SQL script to create the Table, run up a Canvas App auto built on top of it, and add a Gallery as above.
CREATE TABLE [testing].[tbl_Time](
[TimeID] [int] IDENTITY(1,1) NOT NULL,
[StartDate] [date] NULL,
[Hours] [decimal](9, 2) NULL,
CONSTRAINT [PK_testing_tbl_Time] PRIMARY KEY CLUSTERED
(
[TimeID] ASC
) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
