GPC Resources

From Access to PowerApps: Modular Logic using UDFs in PowerApps

User Defined Functions -- or UDFs -- are available in PowerApps PowerApps is acquiring capabilities other coding environments take for granted. UDFs bring reusable, parameterized logic to PowerApps. In this video, I explain the concept and provide three examples of UDFs in Northwind Developer for PowerApps.

From Access to PowerApps: PowerFX Functions vs SQL Server Stored Procedures

Microsoft launched PowerFX Functions earlier in 2025. They sound great! How will they fit your development plans. -Server-side logic -Parameterized Execution, both input and output. -Centralized code A lot like the SQL Server stored procedures we've been discussing, right? In this video I'll share what I know about this tool, positive and negative. I'll share my conclusion about how useful PowerFX Formulas can be and who can best take advantage of them. INCLUDES a short demo.

From Access to PowerApps: Reusable PowerApps Code to Execute SQL Stored Procedures In-depth

An extended review and analysis of my approach to making PowerApps code reusable. Resuable Code is encapsulated in a single function on one screen, similar to a Public Function in VBA. The code fires a set of Stored Procedures to handle CRUD operations for all other screens. In this video, we look at the code that gathers the parameters for the Stored Procedure. CODE SAMPLE You can download the code to create this toggle button on an app code screen from my GitHub site: https://github.com/GPGeorge/NorthWind4PowerApps

From Access to PowerApps: Using Helper Stored Procedures for Modular SQL Logic

In this video, I guide you through a case study using modular, reusable stored procedures to gather and calculate current inventory and product reorder quantities for Products. A set of "helper" stored procedures encapsulate one part of the process. The final step in the "master" stored procedure calculates a reorder quantity and passes it back to the PowerApps Application. SQL Scripts for the stored procedures in this video can be found at https://github.com/GPGeorge/NorthWind4PowerApps/tree/GPGeorge-InventoryandReorderQTY

From Access to PowerApps: The Remaining Order Detail Sub Procedures

A closer look at CRUD stored procedures and the standard error handling stored procedure used in all CRUD master stored procedures. It includes examples of helper procedures and guard code to prevent unwanted results. You can download the code in the stored procedures covered in this video from my GitHub site: https://github.com/GPGeorge/NorthWind4PowerApps

From Access to PowerApps: The Traffic Cop inside CRUDOrderDetails

The inner workings of a typical CRUD stored procedure in Northwind for PowerApps. The master stored procedure implements a"Traffic Cop" to distribute calls to the right sub store proc. The parameters required and how they work. The importance of data validation logic in the stored procedures. Custom error handling CODE SAMPLE You can download the code in the CRUDOrderDetail stored procedure from my GitHub site: https://github.com/GPGeorge/NorthWind4PowerApps

From Access to PowerApps: Data Sources, Binding, and the Case for Named Formulas in PowerApps

This video makes the case for using Named Formulas driven by SQL Server stored procedures in a PowerApps application. It covers data sources available to PowerApps developers, and contrasts bound and unbound objects. Bound forms in Access manage data updates automatically. Lacking that capability in PowerApps, I looked for a way to leverage the power of SQL Server logic via stored procedures to return recordsets and to perform CRUD and Data validation tasks. Links mentioned in the video: Using the Patch command https://youtu.be/yPd0cALExXI https://youtu.be/TxzxWJlESck https://youtu.be/0qbD_BdP3GU

From Access to PowerApps: Migrating Northwind Data

This video pops the hood to look at the tables migrated from Access to SQL Server using the SQL Server Migration Assistant, SSMA. Although we don't look at each table in detail, familiarity with an example table should get your feet on the ground. If you are not comfortable with the principles of Database Normalization, now is a good time to learn. Using SSMA: In this video, I gave a brief glance at SSMA. If you want to learn more, check out the presentation Tom Wickerath and I gave to the Access Pacific User Group: https://youtu.be/ROT1ogVm8a8?si=tthytYIxHziAl0ab

From Access to PowerApps: Replicating Northwind -- The PowerApps App We're Building

This video walks through the Northwind PowerApps App showcasing the use of design patterns that replicate -- to the extent possible -- the Access Northwind Experience. It also introduces the use of SQL Server Stored Procedures to handle CRUD Data Operations and Data Validation in the back end, not in the interface.

From Access to PowerApps: Replicating Northwind -- The Access Template

This video walks through the Northwind Access template. If you are an experienced Access developer, or if you've previously installed the Northwind Developer Template, you probably won't learn much new. If you are a PowerApps developer, or new to Access, this video will set you up for what is to come.

From Access to PowerApps: Replicating Northwind -- Intro

Are you an experienced Access developer wondering how to take your skills mobile? In this series, I’ll show you how I’m replicating the Northwind Developers Edition template from Access—step by step—in PowerApps. This video is an overview and preview of the project.

Optimizing Access Interfaces for Editing w/Remote SQL Server/SQL Azure

In this video, I concentrate on ensuring that users load the minimum number of records for editing. The optimal number of records in an editing form would be one--the one record currently being edited. (This is the corrected version.)

Optimizing Access Interfaces for Remote SQL Server/SQL Azure -- Limiting Selectable choices

In this video, I concentrate on limiting the number of choices offered to the user to improve start up responses. In the next, I'll concentrate on limiting the number of records returned for editing.(This is the corrected version.)

Load Time Comparison for Local and Remote SQL Server

In this video, I compare average load times for a single record with a remotely hosted SQL Server database and a local SQL Server database. The results won't surprise you, probably.

Optimizing Access Interfaces for Hosted SQL Server/SQL Azure, an apology and correction

SQL Server Authentication without Saving User Credentials

You can improve security for an Access/SQL Server application by not saving user credentials in the linked tables' connection strings. You'll learn how to create a cached connection that Access can use to connect to the linked tables. The user need not see or know their UserID and PWD.

Toggle “Password” Input Mask****/Plain Text –A Minor Complication

Toggling the input mask on a textbox control works as expected on the Click event of a command button. It does not work properly on the Click event of an image control. Here's the explanation and a simple work-around.

Toggle Input Mask for Password Textbox Controls

This short video shows you one way to toggle a password to plain text in an Access login form so users can verify they've typed the correct characters before submitting the password for validation.

Migrating “hidden” indexes on fields with Foreign Key constraints, Part 2

In this video, the 2nd of 2, I explore in more detail, what these indexes appear to me to be, why they are on fields involved in a relationship with Referential Integrity enforced, and why SSMA doesn't, and shouldn't, migrate them as indexes.

Identify “hidden” indexes on fields with Foreign Key constraints, Part 1

Did you know that Access creates indexes on fields designated as Foreign Keys whether you ask for those indexes or not? You probably didn't know about them because those indexes do not appear in the Index property sheet for tables. In this video, you'll learn how to expose hidden indexes on a table and why you might want to do that.

Import HTML Files into Access Tables and Correct Data Type of Fields

In this short video, I demonstrate converting short text fields in an HTML import file to Date/Time Fields in an Access table.

My (somewhat belated) Return to Classic Client Server Design for PowerApps applications

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.

The Most Important PowerApps Flow for Access Developers to Know

Access developers have always had the power of reusable VBA Functions and Subroutines in their database applications. Adding PowerApps to our toolkits can seem like a step backwards when we learn that reusability is not quite so easy. On the other hand, Access developers who use SQL Server know that SQL Server has its own powerful tools: Stored Procedures and Functions. The brilliant part is that one simple pattern for PowerApps flows can bring all of that SQL Server processing strength into our Power Apps applications. I'll show you how. ERRATA: I noticed that around the 8 minute mark, I referred to "creating a view". I should have referred to , "creating a flow" to invoke the Stored Procedure. The error survived both recording and editing of the video. 😳 Link to Shane Young's Video on creating PowerApps flows. https://youtu.be/vpjf5mD-COs?list=TLPQMDIwMTIwMjRHlyqQyynJ9A

Reusable PowerApps Code-- Maybe Not Your Typical VBA Function, but It Works!

Access developers have always had the power of reusable VBA Functions and Subroutines in their database applications. Adding PowerApps to our toolkits can seem like a step backwards when we learn that reusability is not quite so easy. However, there is a way to create reusable code which has at least some of the advantages that offers. This video shows you how. Reusable code across screens: https://blog.consideritman.com/2019/11/24/reuse-code-in-powerapps-across-screens/ https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/Code-re-usability-in-PowerApps- using-User-Defined-functions/ba-p/672998 https://congruentx.com/how-to-create-reuse-functions-in-power-apps/ https://www.youtube.com/watch?v=jIbVssqoatE&t=79s

Wanna Feed Your Need for Speed? Named Formulas instead of Collections for PowerApps

How fast does your PowerApps application load and start? Not fast enough? Try replacing an overloaded StartUp with Named Formulas where possible. You'll be glad you did. In PowerApps, app level Formulas perform most of those same roles and enhance a PowerApps application in the same ways. I show you how it works, including tips on off-loading filtering and sorting to the database behind the app. There are trade-offs, such as flirting with the Delegation limit for large record sources. Overall, the benefits outweigh the risks. And the whole app is easier to manage into the bargain. Link to Shane Young's video that inspired this revision: https://youtu.be/jc2DquMS7qM

Can I load 300,000 Records into a PowerApps Application? Yes! But check out what happened next!

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

Should I use Collections, Named Formulas or Tables for Gallery Items? Here's My Choice, and Why

After comparing speed and usability, I came to a 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 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.

Let users know how many records are in the fully populated gallery without scrolling to the end.

Let your users know how many total records are going to be loaded into a gallery by querying the underlying record source. By default, galleries load records in groups of 100, so the user is left wondering, "Is that everything?" When Delegation Limits also come into play, the problem can be even more difficult. I solved the dilemma, except for one minor detail, 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 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

The Demo: Wanna Feed Your Need for Speed? Named Formulas instead of Collections for PowerApps

See how fast does your PowerApps application can load by replacing an overloaded StartUp with Named Formulas where possible. You'll be glad you did. In PowerApps, app level Formulas perform most of those same roles and enhance a PowerApps application in the same ways. I show you how it works, including tips on off-loading filtering and sorting to the database behind the app. There are trade-offs, such as flirting with the Delegation limit for large record sources. Overall, the benefits outweigh the risks. And the whole app is easier to manage into the bargain. Link to Shane Young's video that inspired this revision: https://youtu.be/jc2DquMS7qM Link to the companion 21 minute video describing Name Formulas in this app: https://youtu.be/1_4sllvUaSM

Building the LTF Searchable Catalog-Part 9-PowerApps Formulas

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.

App Variables and Application Bias

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.

Context Variables or App Variables

A good naming convention helps keep variables sorted out. In PowerApps, variables can have App-wide, or global scope, or they can have screen level, or context scope. Using them correctly requires understanding the difference and when to use each. UpdateContext() creates local, or screen level variables. Set() creates App-wide, or global variables. My new naming convention helps me keep that straight. In Part 7 you learn the difference between the two and see an example of why it matters.

Patch Records in the source table - Lessons from unbound Access forms

Bound or unbound interface objects in PowerApps? It's axiomatic that bound forms are the most effective way to manage data in Access. Is that also true in PowerApps? I argue that it's not. In Part 6 you review the Patch command, which allows you to append or update records in the underlying data source in a powerful, flexible way that I find preferable to trying to use Forms.

Appropriate Roles for List and Detail Views in the PowerApps Interface

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.

Leverage the Power of SQL Server Views to streamline data logic problems

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.

Display Many-to-Many Relationships in PowerApps Galleries: Books and their Authors

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.

Filtering and Searching ensures you load the smallest possible number of records in a gallery

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.

Why I Helped the Lander Trail Foundation Create a searchable online catalog (NO internet needed)

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.)

Northwind Stock Take Scan without a Key Pad

This presentation demonstrates bar code scanning without a key pad to minimize key strokes for Stock Take.

Click-Point-Count-Save -- A More Detailed Review of Scanning SKU Codes for Northwind Stock Take

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.

Click-Point-Count-Save -- Overview of Scanning SKU Codes for Northwind Stock Take

Bar code 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.

Click-Point-Count-Save -- Scanning SKU Codes for Northwind Stock Take

This introduction demonstrates the bar code scanner in action and provides and overview of main features.

Date Logic in Report Grouping - I

Learn to use DatePart(), DateAdd() and DateValue() to create summary reports with grouped details

Mike's Mobile Library - Getting Data from the Google Books API with a Custom Connector

Why type all of the details about books in my library when I can just "ask Google" to give it to me. I had to create a new, custom PowerApps connector for Mike's Mobile Library to retrieve the data, and figure out how to request and use the resulting JSON file.

SQL Server Real DataType in a Linked Table in Access Unexpected Behavior

https://youtu.be/s9eL0MQ8_H4

First() and Last() Versus Min() and Max()

Learn why experienced MS Access developers prefer the Min() and Max() aggregate functions over the First() and Last() aggregate functions.

Mike's Mobile Library - Use a Custom Connector to retrieve JSON files from the Google Books API

Why type all of the details about books in my library when I can just "ask Google" to give it to me? I had to create a new, custom PowerApps connector for Mike's Mobile Library to retrieve the data, and figure out how to request and use the resulting JSON file. In Part 3, I teach you how to use it.

Mike's Mobile Library - Capture Images for your PowerApps Application with your Camera or Your Phone

Learn how to use your camera to take pictures, store them "in the cloud" and use them in your PowerApps Application.

My Hybrid Shopping List -- Intro to Hybrid Access/PowerApps Application concepts

What is a Hybrid Application? Why would you want one? This video is Part One of a four-part series.* Part One introduces the concept of Hybrid Access/PowerApps applications and explains why Access developers need to learn more about the hybrid approach.

Building Relational Tables -- Adding TransactionTables I

Continue building a set of relational tables to track work on projects

Date Logic in Report Grouping -- II

Learn to use DatePart(), DateAdd() and DateValue() to create summary reports with grouped details

One-to-Many or Many-to-Many?

Should you design related tables as one-to-many, or should they be many-to-many?

Access Navigation Pane: Creating Custom Categories

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

My Hybrid Shopping List - AI Powered!

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.

UA Demo -- Preview of SSMA for Northwind

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/

Access Handles Date Time Extended values, right? Right? Let's Find Out.

Access has supported Date Time Extended for some time, but there are gotchas. This video demonstrates some of them.

Mike's Mobile Library - Create a Custom Connector to retrieve JSON files from the Google Books API

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/

Mike's Mobile Library - Link Images to Records in a SharePoint List - Hyperlink Field

Capture images, store them to an Azure Storage Account and create a hyperlink to the image from a record in a SharePoint list in a single step.

My Hybrid Shopping List -- PowerApps Collections

This video is Part Two of a four-part series: 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.

Store Company Logo Image Files in the Image Gallery

Use the Image Gallery in Access to pre-load one or more company logo images to use in your forms and reports. Minimize database bloat by reusing the images. Avoid lost, externally saved, files. Images travel with the accdb.

Mike's Mobile Library -- Would I do it all again?

Lessons learned from creating a PowerApps application from start to finish. The good parts as well as the less good. Access and PowerApps are great partners in the quest to create hybrid relational database applications.

My Hybrid Shopping List -- "Interim" Collections and Persisted Tables- Revised

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.

Mike's Mobile Library -- Validation Feedback vs Silent Deletions

Learn how to provide user-friendly feedback to avoid silent failures and silent deletions. Although PowerApps do support notifications, they are easily missed on the screen. Some actions, when invoked by an On Select, do not allow users to confirm that they did, indeed, intend that action, leading to inadvertent data changes.

My Hybrid Shopping List -- Filtering, Searching and Sorting PowerApps Collections

Learn how SharePoint supports Version History for PowerApps applications and for data and records in SharePoint lists. You can recover data recently deleted by accident. You can arrange to be alerted is users change data in any way. Although not full auditing capability, these tools provide a substantial level of security for your PowerApps data.

Mike's Mobile Library -- Versions, Update Alerts and Recovering Lost Data From the Recycle Bin

Learn how SharePoint supports Version History for PowerApps applications and for data and records in SharePoint lists. You can recover data recently deleted by accident. You can arrange to be alerted is users change data in any way. Although not full auditing capability, these tools provide a substantial level of security for your PowerApps data.

Mike's Mobile Library - Maintaining Lookup Tables

Lookup tables are esstential in an application, but setting up maintenance screens can be tedious. Should you copy/paste or duplicate existing screens for additional lookup screens?

Mike's Mobile Library -- Pop Up Dialogs for PowerApps

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 on based on this article: https://www.spguides.com/powerapps-popup-message-box/

Mike's Mobile Library -- Managing Global Variables and Default Values for Galleries

One of the ironies of a "Low-Code" application like PowerApps is the proliferation of "copy-paste" code. Access developers will miss the ability to consolidate reusable code into public modules--something that is not supported in PowerApps. In this video, you'll learn the secret of using Lookup() to set a Default value for a gallery. For an excellent description of an Access relational database application interface that manages Many-to-Many relationships, take a look at Mike Wolfe's blog: Many to Many Paired Lists

Mike's Mobile Library -- Add Records to a Junction Table (Many-to-Many)

Learn how to use paired Galleries to select and add new records to a Junction Table in a PowerApps Application. In an Access relational database application, we would use a main form/sub form design to accomplish this.

Mike's Mobile Library -- One-to-Many Records Using Nested Gallaries

Learn how to display related records in a on-to-many relationship on a PowerApps screen using nested galleries. Links to more detailed presentations on Nested Galleries. These presentations are the best explanations I've seen on the subject. Daniel Christian on PowerApps Nested Galleries: Nested Galleries Paul O'Flaherty on Nested Galleries: Nested Galleries

Mike's Mobile Library -- I Was Wrong About ShowColumns() ... But I Figured It Out!

I had misunderstood how ShowColumns() -- and DropDolumns() and AddColumns() -- work when creating and using collections with SharePoint lists. It was causing me heart-burn. I eventually took the time to do more thorough acceptance testing, discovered the error, and corrected it.

Mike's Mobile Library -- SubmitForm() to Manage Basic Add/Edit Tasks for Publishers -Revised

Learn how to use the basic SubmitForm() action in PowerApps to add new records to a data source or edit existing records in a data source. In this video I use the Publishers screen with a gallery and a form to manage a table of Publishers. Update: After completing this presentation, further testing revealed that I had been using the "DropColumns" and "ShowColumns" actions inappropriately. I'll do a follow up video explaining that. Basically, a collection retains ALL of the SharePoint generated fields with or without ShowColumns() and DropColumns() .

Mike's Mobile Library -- Variable Variable Values in Add and Edit

Learn how to conditionally set different values for variables in PowerApps depending on whether you want to Add a new record or Edit an existing record.

Mike's Mobile Library -- Add or Edit SharePoint Lookup Fields in PowerApps

Learn how to save new or changed values in complex columns (Lookup Fields) in SharePoint lists using the Patch Command. LookupColumn Syntax for Patch LookupColumnName: { &nbsp&nbsp&nbsp&nbsp // '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference", &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Id: _Lookup ID_, &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Value: _Lookup Value_ }

Mike's Mobile Library --OnSelect, SetVar and Patch to Edit Existing Records

Learn to use the Patch() function to edit existing records in a table using PowerApps. SetVar() creates a variable to determine whether to add a new record or edit an existing record. OnSelect() triggers the Patch() action. In this session, we develop the Patch() command code to edit existing records in a SharePoint list via PowerApps. Conditional logic in the OnSelect() action determines whether to add a new record or edit the selected record. We follow the edit branch in this session.

Mike's Mobile Library --OnSelect, SetVar and Patch to Add New Records

Learn to use the Patch() function to add new records to a table using PowerApps. SetVar creates a variable to determine whether to add a new record or edit an existing record. OnSelect() triggers the Patch() action. In this session, we develop the Patch() command code to add a new record to a SharePoint list via PowerApps. Conditional logic in the OnSelect() action determines whether to add a new record or edit the selected record. We follow theAdd branch in this session.

Mike's Mobile Library -- PowerApps and SharePoint Records and Fields

Learn why Lookup Fields in SharePoint lists present a unique challenge for Access Developers working with PowerApps. Learn why they can be confusing to experienced Access Developers and how to handle them successfully in DropDown input controls on PowerApps forms and galleries.

Mike's Mobile Library -- Filter Items in the Publications Gallery

Learn how to filter items in a PowerApps gallery control. Search and Filter using input box and dropdown controls. Plus a Naming Conundrum exposed.

Mike's Mobile Library -- Is it "ID" or Is It "PublicationGenreID"? You decide.

SharePoint automatically creates ID fields for all lists. ID fields are similar to Access AutoNumbers in that they are automatically created for each new record, they are not reused and they must unique. You can rename them to provide better visibility to what they are used for. However, sometimes PowerApps ignores that alias and uses the original default name "ID". Be aware of this tendency and don't let it be confusing.

Mike's Mobile Library - Populate the Publications Gallery with Book Titles

Place a gallery on the publications screen. Wire up a collection for the data source of the gallery. Link mentioned in the Video: Creating Collections in PowerApps

Mike's Mobile Library - Setting Up Filters for Publications

Create Input Boxes and Dropdowns to Filter records in a PowerApps Gallery. Using Dropdowns or Combo boxes: your choice.

Mike's Mobile Library - Setting Up the Screen Header

Synchronize elements of a PowerApps screen Header using X and Y position coordinates and control width and height.

Mike's Mobile Library - PowerApps Screens Galore

Take a brief pause from building Mike's Mobile Library. In addition to the basic blank screen, PowerApps has several templates for commonly used specialty screens. They are wired up and ready to use as soon as you insert them into your PowerApps application.

Mike's Mobile Library - Creating a "Responsive" Layout

In this session we learn how to synchronize position of controls on a screen using X (horizontal) and Y (vertical) coordinates and width and height of each control.

Mike's Mobile Library - The Start Screen

In today's presentation, we set up the Main Menu, or Start Screen, by copying the Start Screen objects from the Production version and changing the theme colors from Production blue to Development Orange.

Mike's Mobile Library - Connect Data to the PowerApps Application

In this presentation, we connect our Development version of Mike's Mobile Library to the SharePoint lists in the Development subsite. We also connect to the Azure Blob Storage Location where our cover photos are stored. Link to the Azure Blob Storage Account Video: Link to Azure Blob Storage Accounts Link to setting up a PowerApps App to use SharePoint lists: Setting up PowerApps to use SharePoint lists Note: A lot has changed in 3 years, but this video is still a good starting point for using SharePoint lists.

Mike's Mobile Library - Create the Options Screen and OnStart Event

In today's presentation, we start setting up our PowerApps application in Development mode by creating and populating the options screen and copying the OnStart code into the new Dev copy of the application. We also get to preview an entirely NEW feature in PowerApps. the StartScreen option. Read about it here: The PowerApps Startup Screen

Mike's Mobile Library - Set up a Development Environment in PowerApps

Let's build a PowerApps application to manage the catalog for a personal library! On Day 4, we begin by preparing to set up a Development version of the PowerApps Application.

Mike's Mobile Library - The SharePoint Lists for Data

Let's build a PowerApps application to manage the catalog for a personal library. On Day 3, we review the Tables (SharePoint Lists) behind the application.

Mike's Mobile Library - The PowerApps Interface

Let's build a PowerApps application to manage the catalog for a personal library! On Day 2, we review the PowerApps interface.

Mike's Mobile Library - The Access Interface

Let's convert an existing MS Access relational database application to a PowerApps application! Mike's Mobile Library is a catalog of books, DVDs and periodicals and the authors or creators who wrote and produced them. It manages transfers in and out of the library via purchase or lending. Day one is an overview of the environment and a review of the existing desktop Access front end.

PowerApps 4 Access Devs --10, 000 Records -- 2nd Look

In a second look at the process of loading 10,000 records into a PowerApps application, we consider the positive impact on performance increasing data row limits from 500 to 2,000 records and the negative impact on performance of the presence of Lookup fields in a gallery. We introduce the Patch() function to add add new records to a table. We look at the use of the PowerApps StartUp event to set the value of global variables. A list of resources used for this application is available in the description of the initial video.

PowerApps 4 Access Devs 10,000 Records -- Oops!

I identified and corrected a logic bug (wrong math approach) which appeared during repeated testing. Funny how that works, eh? I also changed the workflow for the application after reconsidering the impact on performance that comes from repeatedly (and probably unnecessarily) re-populating a large collection. Note: On my smart phone, startup takes as little as two or three seconds. I think the difference is partly due to the heavy processing load in the development environment which is also competing with recording software for resources.

PowerApps 4 Access Devs - Load 10,000 Records

10,000 Records in a Database Application? Really? Access developers work with large recordsets, sometimes hundreds of thousands of records, so the idea of being limited to 500 (or even 2,000) seems unworkable. Fear not, there is a way to load thousands and thousands of records into your PowerApps application. This video shows you how. It also shows you why that's actually not a good idea.

Formatting Dates for Display in MS Access

Use MS Access Format() expressions to display dates and dates with times to your users in the interface.

So You Want to Build a Microsoft Acccess Database

MS Access is a great tool for bulding relational database applications, but you have to know and follow sound design principles to the underlying considerations on which a good Relational Database Application must be based

Creating Your First Table in a Microsoft Access Relational Database

Step by Step Process of Creating a table in an Access Relational Database. What fields need to be included and why.

Creating Your Second table in a Microsoft Access Relational Data

Adding a related table to your MS Access Relational Database requires careful thought. Properly defined Primary and Foreign Keys make the second table work with the first.

Reconciling Business Rules and Normalization

Normalization, or or the rules for creating a set of relational tables, is only part of the process. You also need to reconcile good table design with the business requirements that govern your database application. There may be more than one way to go, and determining which way will best support the business rules is a big part of doing a good job.

One-to-Many or Many-to-Many -- and Why Part II

Microsft Access Relational Databases capture information about relationships between two or more entities: People and Email Addresses, Classes and Students, Invoices and Line Items on the Invoices, Meals and Food Items in the Meals. Some of those relationships are one-to-many. Some of those relationships are many-to-many. In this video, you'll learn about both types, and you'll see how to create appropriate related tables for each type

Relational Tables -- Summary and Review

A solid, stable, relational database exhibits several qualities that depend on a good foundation of sound table design.

Calculated Values in Queries and on Forms

Learn to use Expressions to calculate values for display on Forms. Calculated values can also be used for sorting and filtering in queries.

Performance Analysis Using Date Logic and Conditional Formatting

Performance Analysis Reporting in Access Using Date Logic and Conditional Formatting to Highlight Results

Date and Time Gotchas in Access Reports and Queries

Two ways in which unwary Access users can create misleading or inappropriate reports because of failure to account for how Access actually stores dates with times.

Date Formatting and Filtering in Access Reports Filter Criteria

Using tempvars to set criteria for date range in an Access Report

Default Dates and Date Formatting & Grouping in Access Reports

How to Use Default values for dates, grouping and sorting on Formatted Dates

Date Formatting for Access Form Filters

Using Custom Date Formats and Date Logic to filter subforms and to improve appearance of the data displayed.

Date Formatting in Access Forms

Introduction to Display Formatting for Access Dates and Times in Forms How to use custom formatting to improve appearance and conserve space on a crowded form.

Date Formatting In Access Tables I

MS Access stores dates internally as numbers. It's up to the developer to format those dates properly for display.

Date Formatting in Access Tables II

The impact of Display Formats on Date/Time Fields In Access

Date Formatting in Access Tables III

A closer look at the display possibilities available for date/time fields in tables

Editing Office Ribbons with a Third Party tool

The steps required to modify a Custom Access ribbon using the IDBE Ribbon Creator Tool