Introduction.
I used to touch Office365 PowerApps.
PowerApps is a rather obscure tool, and I had neglected it, thinking that I would forget how to use it if I didn't write down how to use it.
I haven't touched the application in a while to fix it, and as I thought, I forgot all about it, so this time, I'll make a note of how to use it, focusing on the parts I got stuck on so I don't have to worry about forgetting it again anytime soon.
Because of the volume, the article has been divided into the following three sections, and this article is the second.
Part 1: Basics
Microsoft (Office365) PowerApps Usage Memo.
No. 2: Data-related
Microsoft (Office365) PowerApps Usage Memo (Data Sources and Collections)
No. 3: Other notes
Microsoft (Office365) PowerApps Usage Notes (Other)"
How to Use Data Source Collections
What is a data source?
Table data external to PowerApps, such as Excel files, can be registered as a data source and used within PowerApps.
Sample Creation
Since the use of data sources is difficult to understand at first glance, we recommend that you first prepare a test data source and then create a sample application in the wizard based on that data source to see how it works.
The sample application provides the minimum functions necessary to work with data, such as listing data from the data source, displaying details, updating and deleting values, and adding new records for selected records.
Creation of Excel data source
When it comes to data, Excel files are often used,Excel files cannot be used as a data source for PowerApps as is.Therefore, it is necessary to pay attention to the following.
Excel allows you to write values in any cell you like, but because of its freedom, it is not suitable for representing data with fixed columns and data types, as in a database table.
Excel has a function to create a table with a specified range of data, columns, and data types.
The data sources that PowerApps can read are,This does not apply to the entire Excel file, but to the tables created within that Excel file.
How to create Excel tables
Suppose you want to create a table from the following data
- Select a range of data including headers
[Home]-[Style]-[Format as Table]
Then "Table 1" is created from the selection.
Multiple tables can be created on a single sheet.
The table name can be changed to [Formula]-[Defined Names]-[Manage Names].
Creating Sample Apps
Create a sample application from the above Excel table.
- Upload Excel to OneDrive
[PowerApps]-[+Create]-[Start with Data]-[OneDrive]- A list of files on OneDrive will appear. Select the uploaded Excel file.
- A list of tables in the Excel file will be displayed, so select the table you want to use.
Then, based on the table,
- Listing of table information
- Detailed view of selected records in table
- Change the value of selected records in a table
- Add a new record to the table
If you are making an application and have any questions, I think this would be a good place to start.
Use data source from 1.
Next, I will explain how to use the data source, not in the sample, but in my own application.
Data Source Registration
Register the data source with [View]-[Data Source]..
The data source is registered for the app
Listing of data source contents
PowerApps has two components, "Gallery" and "Data Table," that list the contents of a data source, so to display multiple records,Always use one or the other.
- Insert component with
[Insert]-[Gallery]or[Data Table] - Select a component and choose a data source for the "Items" property that indicates the data to be displayed in that component ("Table 1")
The contents of the data source will then be displayed in the component.
Linking data source columns to display components
The [Properties]-[Fields] determines which columns of the data source are associated with which components.
If you have specified a data source but the values are not displayed,Most of the time, this string is not tied.
Figure with "col1" and "col3" interchanged.
Filter and sort data to be displayed
If you specify the data source directly in "Items," all data will be displayed.
To filter or sort the displayed content, use the filter and sort functionsMake sure that the result is specified in "Items".
For example, data sorted by "col2" would be SortByColumns(table1, "col2").
Detailed data display
In creating a UI for working with individual records from a list, it is easy to get stuck if you are not aware of the PowerApps data structure, so we will briefly explain it first.
First, consider a series of data exchanges when clicking on a single record from a list of multiple records, such as a data source, to display its details.
When I first came into contact with PowerApps, I mistakenly thought that when I clicked on a record, I was taking data from it and assigning the data to the detail view component.
In fact, in PowerApps, screens, components, data sources, everything,global objectIn,Objects persist even if the screen transitions。
So, the value setting of the detail view component can be specified with an object that does not exist on the screen, such as "the element of the first record of the list object before the transition that you clicked on.
Based on the above, we now proceed to the detailed explanation below.
The detailed display of a single record of a data source is done in the "Display" component of the "Form".
- Specify the data to be displayed with
[Advanced]-[Data]-[DataSource]. - Specify which record of the specified data by
[Advanced]-[Data]-[Item].
Example: For records selected in the "Gallery
Set "Item" to <gallery name>.Selected.
Example: If "col1" is "1" record
Set "Item" to First(Filter(<data source name>, col1=1)).
The Filter() function alone is a multi-record table data.The First() function is often used to extract a single record from table data.
Example: The record selected in "Gallery1" above is displayed in "FormViewer1" below.
Data Update
Editing a single record in the data source is done in the "Edit" component of the "Form".
- Specify the data to be displayed with
[Advanced]-[Data]-[DataSource]. - Specify which record of the specified data by
[Advanced]-[Data]-[Item].
And thisBefore the component is displayed, initialize the editing component with EditForm(<component name>) and then display the component.
Components should be initialized before their screen transitions, as in the sample app.
After a user changes a value on the form, an update is performed with SubmitForm(<component name>). The data source record will then be updated.
Depending on the results of the update run, the component's
OnSuccessOnFailure
property is called, the process after a successful or failed update is described here.
New data registration
Editing a single record in the data source is done in the "Edit" component of the "Form" as well as updating.
- Specify the data to be displayed with
[Advanced]-[Data]-[DataSource].
And thisBefore the component is displayed, initialize the editing component with NewForm(<component name>) and then display the component.
Components should be initialized before that screen transition, just as they are for updates.
After the user has set the values on the form, a new registration is executed with SubmitForm(<component name>). The new record is then added to the data source.
The result of the new registration will determine the component's
OnSuccessOnFailure
property is called, the process after success or failure is described here.
Deletion of data
Deletion of records is done by the following function, independent of the form.
Remove(<data source name>, <record to be removed>)- To delete multiple records, use
RemoveIf(<data source name>, <condition for record to be removed>).
The collection is then described.
collection
What is Collection?
A collection is a collection that can be created arbitrarily inside PowerApps,Array Global Variablesof the
It can be treated like a data source.
Creating, adding, and deleting collections
- Made as:
ClearCollect(<array name>, {<item name1>:<item value1>, ...}) - Add value:
Collect(<array name>, {<item name1>:<item value1>, ...}). - Delete:
Clear(<array name>)
If multiple values are used, use the Table() function to group the values together.
ClearCollect(<array name>, Table({<item name1>:<item value1>, ...}, {<item name1>:<item value1>, ...}, ...))
Differences from Data Sources
Collections are similar to data sources, but are not very flexible and cannot do the following
- No value changes or record deletions are allowed afterwards (all records can be deleted with
Clear()). - Form-related processing is not available.
The following is a summary of the other items and more, but since it has become lengthy, I have decided to make it a separate article.
