GeorgeHepworth

App variables, or Global variables, make it possible to pass data between screens in a multi-screen application. There are good ways to do that, and better ways to do that. In part 8, you learn how to do it more efficiently. Application Bias is the subconscious belief that once you've mastered one development environment, those skills automatically enable you to work in a new environment. While there are significant similarities, it's wise to be alert to the subtle differences as well. I explain how I fell into that bias and how I learned to overcome it.

(There is no audio for this one. Follow the cursor as I move through the screens.) How well does Access actually handle Extended Date/Time fields? I demo some problems.

Should you kick off searches as soon as a user selects one search parameter, or should you defer searches until all search parameters have been selected? Should the search be automatic or under the control of the user? I show you how to implement each strategy. You choose the right one for your application. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone.

PowerApps offers a lot of tools to filter, search and sort records, some of which are not natively available in Access. It turns out, however, that good design is the same in both environments. Ignore that at your own peril. Let my experience be a lesson. Some of the code used is available as a PDF at: https://www.gpcdata.com/downloads/ParameterizedCollection.pdf The code is too long to paste here in the description. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone.

Named Formulas are a nod in the direction of reusable code as well as offering performance improvements. Let's see how to create and use them.

A short demo of load time achieved by using Named Formulas

As Access developers, we're used to calling Procedures from multiple places in our code. PowerApps, on the other hand, doesn't directly support code reuse. Here's a work-around to overcome that limitation.

PowerApps has a delegation limit of 500 to 2000 records at a time. What happens, then, if your record source contains 10s of thousands or even 100s of thousands of records? Can you put them in a collection and avoid that limit? Let's find out. Links to downloads: https://www.gpcdata.com/downloads/Employees2K7.zip https://www.gpcdata.com/downloads/Employeesbacpac.zip

For this video, I compared speed and usability for three options. Using a collection for the items in a gallery. Using a named formula for the items in a gallery. Using the source table directly for the items in a gallery. I came to a somewhat unexpected conclusion. In this video I offer some guidelines to help you choose between Collections, Named Formulas and Tables for Gallery Items. Let me know in the comments if you agree or disagree. This is a companion video to "Load 300,000 Records into a PowerApps Application?" Links to downloads: https://www.gpcdata.com/downloads/Employees2K7.zip https://www.gpcdata.com/downloads/Employeesbacpac.zip

PowerApps galleries load records in chunks of 100, so a user is left wondering, "Is that everything?" When Delegation Limits also come into play, the problem can be even more difficult. If the gallery contains exactly 500, or 2,000 records, is that all there are, or is that a result of delegation? I solved the dilemma with a PowerApps flow and a Stored Procedure in SQL Server. Here's the SQL from the Stored Proc. The rest of the code is revealed in the video. USE [NameOfYourDatabaseGoesHere] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE OR ALTER PROCEDURE [dbo].[RecordCount] @SearchTerm varchar(10) = '' AS BEGIN DECLARE @Recordcount int SELECT @Recordcount = count(*) FROM dbo.Employees WHERE LastName like '%' + @SearchTerm + '%' OR FirstName like '%' + @SearchTerm + '%' RETURN @Recordcount END GO Links to downloads: https://www.gpcdata.com/downloads/Employees2K7.zip https://www.gpcdata.com/downloads/Employeesbacpac.zip

Access developers can use Public Constants and Public Enums to enrich their functions and procedures. There are several advantages, including readability and consistency and consolidating definitions in a single location. In PowerApps, app level Formulas perform most of those same roles and enhance a PowerApps application in the same ways. I show you how. Link to a video demonstrating user controlled defaults. https://youtu.be/OnUKMRFurgA If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone.

Deploy a smart interface: separate roles for List and Detail views. In Part 5 you review the most effective and performant way to display data to users and also offer them a way to edit that data as required. The initial list of records, or items, is read-only and formatted for presentation. To modify one of those items, a good interface also provides a detail screen in which all data points are open for edits. Compare this interface in a browser-based app to those you've created in Access. List views are read-only for quicker display. Detail views are editable. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone.

Leveraging the greater power of the SQL Server database engine In Part 4 you learn how to use the greater data logic and data manipulation power of the SQL Server engine to shape data for display in an interface. Although this example is relatively simple, it illustrates the principle that you should try to use the SQL Server (or other database engine) to perform data logic tasks whenever possible. Save the interface resources for tasks related to displaying the information. I explain the design of the view which is used to populate a collection in the PowerApps application and explain why it's a better solution than doing the data lookup and concatenation directly in the interface. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone.

Displaying related records in a gallery and sub-galleries In Part 3, you learn how to recognize many-to-many relationships in a relational database. You also learn how to display the related records in the PowerApps interface. The records in one table populate a gallery. The records in the related table populate embedded sub-galleries in that main gallery. I show you how to set up the sub-galleries and return the records needed to populate them. I also show you how to create in-memory, temporary tables, or Collections, in PowerApps to minimize the number of times your application has to ask the database for those records. This technique is especially valuable for recordsets larger than the delegation limit in PowerApps. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone. Other Videos on Sub-galleries and retrieving large recordsets into collections. Retrieving 10,000 records in PowerApps: https://studio.youtube.com/video/qlJAJeo7yDM/analytics/tab-overview/period-default Mike's Mobile Library -- One-to-Many Records Using Nested Gallaries: https://youtu.be/0I7LllbROT0

Searching and Filtering items in a gallery. Load one record at a time. In Part 2, you get to look at the techniques I used to filter nearly 2,000 records to load and display the minimum number of records possible. If you can't connect to the internet, how do you create an online, searchable database for a research library? The LTF Searchable Catalog is a full-featured app which can be supplemented with an Access desktop interface. Because of the unique environment and restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone. Other Videos on Filtering and Searching: Mike's Mobile Library -- Filter Items in the Publications Gallery -- https://youtu.be/cxevCxStJPs Mike's Mobile Library -- Setting Up Filters for A Gallery - Layout -- https://studio.youtube.com/video/XQWsw7xgZA8/edit My Hybrid Shopping List -- Filtering, Searching and Sorting PowerApps Collections -- https://studio.youtube.com/video/da_a_VlZOOY/edit

What the the Lander Trail Foundation is and why I helped them create on-line searchable catalog of books. If you can't connect to the internet, how do you create an online, searchable database for a research library? This video is the kick-off for a series explaining the why, what and how of the LTF Catalog in PowerApps. I explain the goal of the Lander Trail Foundation Searchable Database project and the limitations imposed by the environment which made PowerApps with a hosted SQL Server database the right choice. (Hint, "online" means a connection to the internet.) The LTF Searchable Catalog is a full-featured application which is supplemented with an Access desktop interface for back end reporting and analysis. Because of the unique environment and certain restrictions in the LTF museum, the interface must run on a cellular-enabled device, i.e. a tablet or smart phone. In Part 2, I'll demo elements of the application in action, and start previewing the methods and techniques required to make it work effectively. Many of the design principles and techniques are based on lessons I've learned over a 20+ year career creating Access desktop database applications.

The Northwind 2.0 Developer Edition template showcases major features of Access; it is not designed to run a company nor show you how to build such an application. Northwind is a fictitious trading company whose customers are independent grocery stores. Managing Inventory is critical. Regular Stock Take events ensure Northwind always knows how much inventory they have available to sell. Bar codes scanners allow Stock Takers to scan SKU codes on Products and count inventory on hand in a warehouse or other location, with or without an internet connection. Both Model-Driven and Canvas Apps now support offline mode, but Canvas Apps must be in a solution in order to be configured to do so. The PowerApps app in this video is a Model-Driven app. This presentation walks through Stock Take Initialization in the Access interface. Then it drills in on three features in the PowerApps interface for more detailed review. Northwind Developer Edition Documentation: https://support.microsoft.com/en-us/office/northwind-2-0-developer-edition-32eb79d2-bede-4ea4-b575-0714ca8dc1e2 Table of Contents: 00:00 - Scanning SKU Codes for Northwind stock take 00:36 - This will be a deeper look at the functions and code 00:49 - Three Features called out for special attention 00:56 - StockTakeScan - The Dataverse "bridge" table 01:02 - Denormalized version of StockTake table 01:09 - Temporary "Bridge" Table transfers data between two interfaces 01:27 - Initialize stock take in the Access interface 02:03 - Containers for flexible layout of PowerApps interfaces 02:30 - The barcode scanner is the "Star of the Show" 02:44 - Stock take is a System Admin Function 03:05 - Prevent Orders and Purchase Orders during stock take 03:25 - Close Orders for Stock Take command button code 04:10 - Live Data from Dataverse displayed in subform 04:30 - Step through Procedure 05:05 - Run a query to add new products to StockTakeScan 05:18 - Also calculate Expected Quantity 05:55 - PowerApps Development environment Dataverse table 06:46 - In the application, the Options Screen 06:52 - Keypad Left or Right 07:17 - Layout relies on Container Controls 08:34 - Specify "X" position for containers with variables 11:23 - Allow users to Customize the interface 12:16 - Using the Barcode Reader Control 13:05 - Properties of the Barcode Reader Control 13:53 - The Barcode Reader OnScan event 14:17 - Context, or local, variable vs Global variable 15:27 - Parse Unique Identifier from SKU Code 16:19 - Code Stub for Alternate Method of scanning 17:28 - The Products gallery 17:37 - Gallery Items are the functional equivalent of a form's recordsource 17:55 - The Stock Take Collection; what it is and how it is created 20:12 - Initialize the collection in App OnStart 20:22 - App OnStart Action; the functional equivalent of AutoExec Macro 21:59 - Filter the items in a gallery by the SKU code 23:51 - Live Demo on a Smart Phone 24:02 - Connected to the datasource -- Working Online 26:52 - Thank You. Like and Subscribe

Riding the wave of AI excitement, I decided to give it a try in My Hybrid Shopping List App. The result was awesome, but the learning curve was pretty steep. Scan receipts from a shopping trip into the AI Model and let it capture details about the store, the items purchased, the price of each item and the total. Storing that data into a temporary table in the SQL Azure database provides a validation check for manually entered data typed in through the phone. In Part 1 I walk through the initial setup of the AI Model itself and demo its beauty in the PowerApps Shopping List app.

High Level Preview of Using SQL Server Migration Assistant (SSMA) to migrate tables from an existing Access relational database application to SQL Server. A fuller presentation will be offered on April 6th, 2023, in the Access Pacific User Group Meeting. https://accessusergroups.org/pacific/event/migrating-access-tables-to-sql-server-a-comparison-of-methods/

During final prepping of the previous version, I botched part of the video. Corrected in this version. ==== This video is Part Four of a four-part series: Part Four explores two functions needed to make the Shopping List work: -- Persisted tables in the SQL Azure database to retain the current shopping cart between sessions and -- The Patch() command for both data insertion and data update. We wrap up with a look at the final step: moving purchased items from the shopping cart to the permanent database tables in Access. Part One introduces the concept of Hybrid Access/PowerApps applications and explains why Access developers need to learn more about this approach. Part Two lets you experience the PowerApps interface, running in a browser. It explains the concept of PowerApps collections -- which can be compared to temporary, in-memory tables. In Part Two, the emphasis is on using a collection to retrieve a mirror image of a table in the SQL Azure database to improve performance with large recordsets and minimize calls to the server for data. Part Three explores Filtering and Searching a Collection to return the minimum number of records from that collection to the Items in a Gallery. The final step is Sorting the remaining, filtered set of records for the Gallery. Two approaches to filtering are shown and compared for verbosity and performance.

What is a Hybrid Application? Why would you want one? This video is Part One of a three-part series in which I will: -Introduce the concept of Hybrid Access/PowerApps applications. -Explain why Access developers need to learn more about this approach. -Introduce the Access interface, running on a Windows Desktop. In Part Two I'll let you experience the PowerApps interface, running in a browser. I'll also introduce and explain the concept of PowerApps collections and why I think they can be a powerful, flexible tool in a Low-Code interface. In Part Three, we'll explore more of the functions needed to make the Shopping List work, including the use of persisted tables and the Patch() command.

Learn how to create your own Custom Categories for objects in the Navigation Pane. Group objects by their function in your database application.

Capture images, upload them to an Azure Storage Account and link them to a record in a SharePoint List via a hyperlink

Create a PowerApps application which uses your Smart Phone Camera to capture and upload images to an Azure Blob Storage Account.

Learn how to create the experience of a pop up to interact with your users. Provide feedback and confirm actions such as deletions. The technique I used is based on this article: https://www.spguides.com/powerapps-popup-message-box/