web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Coming from an MS Acce...
Power Apps
Unanswered

Coming from an MS Access background

(0) ShareShare
ReportReport
Posted on by 153

Hello. New to Power Apps. In MS Access, it was "easy" to set up Table A, with say an unique ID and then on a form connect that to another subform (using that unique ID) that showed an editable table (like Data Table control, but you could easily edit). How to accomplish please with PA? For example, table A might have ID + name and table B might have ID +several data rows all connected to that one ID. Thanks!

Categories:
I have the same question (0)
  • Scott_Parker Profile Picture
    1,090 on at

    The closest thing Power Apps has to a continuous form is a gallery. But, think of it like a continuous form with no bound fields. The Filter() function will let you retrieve records from table B matching the ID from table A.

     

    A collection is roughly analogous to a recordset and we'll use it to populate the gallery. Create a collection based on your filter results using ClearCollect(). Then assign this collection to your gallery's data source property. Add whatever you controls you want to a galley and map the fields to the appropriate control.

     

    ClearCollect(colData, Filter(TableB, ID = 4))

     

     

    Play the app and change the values within the controls in the gallery. Note that the changes made in the controls DO NOT update the collection as you go along in the way that they would in Access. Nor does updating a collection update the data source it is based on.

     

    Once edits are made within the controls in the gallery, you will need to run a Patch() function to write the records back to your data source. The annoying bit is that you will need to map the values from each control back to the corresponding field in your dataset. The ForAll() function helps here by allowing us to operate across all rows in the galExample.AllItems table.

     

    Patch(
    	TableB,
    	colData, 
    	ForAll(
    		galExample.AllItems,
    		{
    			FieldA: ctrlFieldA.Value,
    			FieldB: ctrlFieldB.Value,
    			FieldC: ctrlFieldB.Value,
    			...
    			FieldN: ctrlFieldN.Value
    		}
    	)
    )

     

  • CedarTree72 Profile Picture
    153 on at

    So basically, compared to MS Access forms, sigh...

  • CedarTree72 Profile Picture
    153 on at

    It seems to me that PA is more focused on having singular records, and not master/child subforms where the subform (like MS Access) is itself a datasheet. Do I have that right?

  • Scott_Parker Profile Picture
    1,090 on at

    Essentially. There is a Form control in Power Apps, but it is only good for single records. It doesn't work inside of a gallery.

     

    A common design pattern is to have a gallery where you select the record you want to edit and then have a form control beside the gallery where you make/save changes to that record. Usually don't edit within galleries themselves because it can be finicky.

     

    Power Apps has no concept of the relational model, at all. Everything is being stored as JSON objects. You have to explicitly code when you want to get records from another table, usually by using the LookUp() and Filter() functions.

  • CedarTree72 Profile Picture
    153 on at

    Thanks! So, two random questions:
    1) How to save the data I just edited (I've connected to a SQL server back-end)? Do I need to add a "Save" button?
    2) How to do a drop-down that queries the SQL server for options, e.g., SELECT DropDownOptions WHERE Type = 'FavoriteColors' FROM TableDropDownOptions. I can manually insert options but prefer to edit the option in SQL server.
    Thanks!!!

  • Scott_Parker Profile Picture
    1,090 on at
    1. Yes, I generally use save buttons. I usually don't try to automatically save records by using events because the available events are severely limited compared to the events available in Access forms.
    2. You would use the Filter() function. Write your formula inside of the dropdown.items property. Power Apps will execute the formula when creating or resetting the control.
  • CedarTree72 Profile Picture
    153 on at

    OKay thanks. So (while I know you don't prefer using the events per se, but just to test things out)...  I have a table in SQL server called usystblProjects, and a field called ProjectStartDate. In PowerApps, I have a text field that links correctly to sqlserver and is called txtStartDate. In the Update event, I have put this: 

    Patch(usystblProjects,ThisItem,{ProjectStartDate:txtStartDate})  But I'm getting a syntax error. BTW, the underlying table as a unique identifier called ProjectID which I also have as a (hidden) text field in PA called zProjectID, so I could reference that if needed. Any help is appreciated. Thanks!
  • Scott_Parker Profile Picture
    1,090 on at

    I'm afraid I've exclusively used SharePoint as a data source in Power Apps. The Patch() documentation from Microsoft doesn't specify exactly how Patch() determines which record is which when writing to the data source. For SharePoint, I presume it is using the ID column since it is always present and is a primary key. I'm not sure how Patch() works for SQL Server.

     

    I recommend asking a new question on the subject if you're stuck. Thanks for the good questions and good luck!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 796 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 327 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 268

Last 30 days Overall leaderboard