Coming from an MS Access background

[CedarTree72]

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!

[Scott_Parker]

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]

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

[CedarTree72]

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]

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]

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]

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]

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]

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!