Hi,
I tried to create a easy App (so I thought) but now I tried so mach ways and have no idea how it works.
I wanna create a App which have as a DataSource a Excelfile. In this are different tables (a.E.: "2019", "2018", "2017",...)
The strcuture of the tables are the same. Their are Names and 30 Dates (1., 2., 3., ..., 30.). In this are the holidays of the Employees stored.
1. | 2. | 3. | 4. | ... | |
Name_1 | 31.03.19 | 01.04.19 | 02.04.19 | 03.04.19 | ... |
Name_2 | 01.05.19 | 02.05.19 | 03.05.19 | 04.05.19 | |
Name_3 | 05.02.19 | 06.02.19 | 07.02.19 | 08.02.19 | |
Name_4 | 01.01.19 | 02.01.19 | 03.01.19 | 04.01.19 |
The App looks like that and have to show which holidays in the year are taken with the date.
With the Dropdown the App loads the Datasource in the Gallery. I realized this with a If-Formula in the Gallery items:
SortByColumns(Search(If(var_JahrTxt="2018";UrlList_2018;UrlList_2019); TextSearchBox1.Text; "Spalte1"); "Spalte1"; If(SortDescending1; Descending; Ascending))
This works. But in the DetailForm1 the formula
If(var_JahrTxt="2018";UrlList_2018;UrlList_2019)
gives a error.
What can I do or is there a easier way to realize this app?
Solved! Go to Solution.
Hi @dobroo
You are getting there. You are in the process of what is called normalizing your data and the goal is to not have any repeating data in a table. The next step would be to have a table of Names that you can add additional information regarding each one. Have an ID column for each name (That is called the Primary key) and then substitute it into your table (it would be called the Foreign key in the table, call it NameID). You could then use a Lookup() in your gallery to bring in the name and any other identifying information regarding that person from the Name table.
Hi @dobroo ,
Have you solved your problem?
Currently, within PowerApps, the DataSource property of the Display form is only supported to connect to a specific data source. The formua does not work within the DataSource property of the Display form.
As an alternative solution, you could consider create one Display form for each year (e.g. 2017, 2018, 2019, ...) in your Detail screen. Then within your Detail screen, DisplayForm1 connects to UrlList_2018, DisplayForm2 connects to UrlList_2019, ...
After that, set the Visible property of the Display form in your Detail screen to following:
DisplayForm1.Visible:
If( var_JahrTxt = "2018",
true,
false )
DisplayForm2.Visible:
If( var_JahrTxt = "2019", true, false )
...
Please take a try with above solution, check if the issue is solved.
Best regards,
Hi @dobroo
I think you have made your app more complicated than it needs to be by having multiple tables. It should be possible to combine your tables into one with a column for year. That way you could filter the table on year. The problem is that you can only have a display (or edit) form with a single datasource. Otherwise you will need to have one for each table.
That was my first try.
But therefore I had to build the sheet like this:
Year | Days | Name_1 | Name_2 | Name_3 | Name_4 | ... |
2018 | 1. | 01.04.18 | 02.04.18 | 03.04.18 | 03.03.18 | ... |
2018 | 2. | 02.05.18 | 03.05.18 | 04.05.18 | 04.03.18 | ... |
... | ... | ... | ... | ... | ... | ... |
2019 | 1. | 02.01.19 | 03.01.19 | 04.07.19 | 01.05.19 | ... |
2019 | 2. | 03.01.19 | 04.01.19 | 05.07.19 | 02.05.19 | ... |
But in this case I have no idea to switch the data for the gallery.
The data will be read for the row and not for the columns.
As example:
1. | 2. | 3. | 4. | ... | |
Name_1 | 31.03.19 | 01.04.19 | 02.04.19 | 03.04.19 | ... |
Name_2 | 01.05.19 | 02.05.19 | 03.05.19 | 04.05.19 | |
Name_3 | 05.02.19 | 06.02.19 | 07.02.19 | 08.02.19 | |
Name_4 | 01.01.19 | 02.01.19 | 03.01.19 | 04.01.19 |
Here are the titles
Name_1
Name_2
Name_3
Name_4
and I get in the DetailForm all the datas of the name.
In this case
Year | Days | Name_1 | Name_2 | Name_3 | Name_4 | ... |
2018 | 1. | 01.04.18 | 02.04.18 | 03.04.18 | 03.03.18 | ... |
2018 | 2. | 02.05.18 | 03.05.18 | 04.05.18 | 04.03.18 | ... |
... | ... | ... | ... | ... | ... | ... |
2019 | 1. | 02.01.19 | 03.01.19 | 04.07.19 | 01.05.19 | ... |
2019 | 2. | 03.01.19 | 04.01.19 | 05.07.19 | 02.05.19 | ... |
The titles are
2018
2018
...
2019
2019
and the datas in the Detailform are read from left to right and not for a column.
Can I switch the affiliation? Not left to right but up to down.
You understand my thoughts?
Hi @dobroo
I'm not sure I understand what you have done with your tables. I have written a multi-part discussion of database design in the community blog. Could you please read through it for tips on how to property design your app. It starts with the overview. https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Database-Design-Fundamentals-and-PowerA...
Hi @Drrickryp ,
I have found a solution. Looks (for me) not so pretty, but it works.
Year | Name | 1. | 2. | 3. | 4. |
2018 | Name 01 | 07.03.2018 | 22.06.2018 | 08.11.2018 | 03.07.2018 |
2018 | Name 02 | 05.02.2018 | 19.07.2018 | 03.05.2018 | 23.08.2018 |
2018 | Name 03 | 09.05.2018 | 19.09.2018 | 22.12.2018 | 10.12.2018 |
2018 | Name 04 | 04.03.2018 | 25.04.2018 | 03.02.2018 | 02.05.2018 |
2018 | Name 05 | 30.05.2018 | 05.02.2018 | 07.03.2018 | 02.06.2018 |
2018 | Name 06 | 12.04.2018 | 09.05.2018 | 10.12.2018 | 28.02.2018 |
2018 | Name 07 | 23.06.2018 | 07.07.2018 | 18.10.2018 | 18.04.2018 |
2018 | Name 08 | 08.10.2018 | 18.09.2018 | 28.01.2018 | 24.10.2018 |
2018 | Name 09 | 25.05.2018 | 20.06.2018 | 10.01.2018 | 26.05.2018 |
2018 | Name 10 | 12.06.2018 | 11.05.2018 | 11.07.2018 | 13.06.2018 |
2019 | Name 01 | 25.07.2019 | 17.06.2019 | 18.02.2019 | 02.02.2019 |
2019 | Name 02 | 24.06.2019 | 03.10.2019 | 17.01.2019 | 23.01.2019 |
2019 | Name 03 | 28.07.2019 | 15.09.2019 | 26.05.2019 | 17.08.2019 |
2019 | Name 04 | 04.02.2019 | 02.05.2019 | 16.01.2019 | 18.04.2019 |
2019 | Name 05 | 18.08.2019 | 04.10.2019 | 15.12.2019 | 25.04.2019 |
2019 | Name 06 | 05.07.2019 | 08.05.2019 | 14.09.2019 | 16.08.2019 |
2019 | Name 07 | 19.11.2019 | 26.11.2019 | 28.02.2019 | 17.06.2019 |
2019 | Name 08 | 25.07.2019 | 17.06.2019 | 17.09.2019 | 26.01.2019 |
2019 | Name 09 | 05.04.2019 | 27.09.2019 | 24.06.2019 | 18.04.2019 |
2019 | Name 10 | 26.05.2019 | 25.02.2019 | 26.03.2019 | 21.08.2019 |
In this case I have always to repeat the names for the years.
My thought was to create a 3D table.
Hi @dobroo
You are getting there. You are in the process of what is called normalizing your data and the goal is to not have any repeating data in a table. The next step would be to have a table of Names that you can add additional information regarding each one. Have an ID column for each name (That is called the Primary key) and then substitute it into your table (it would be called the Foreign key in the table, call it NameID). You could then use a Lookup() in your gallery to bring in the name and any other identifying information regarding that person from the Name table.
Ok. I hope I understand everything.
In my case I had another table like
NameID | Name |
XYZ1000 | Name 01 |
XYZ1001 | Name 02 |
And my first Table gets another column with the NameID.
If I filter the Data with Year: "2018" and the NameID: "XYZ1000" I get the this data:
07.03.2018 | 22.06.2018 | 08.11.2018 | 03.07.2018 |
correct?
Hi @dobroo ,
Have you solved your problem?
Currently, within PowerApps, the DataSource property of the Display form is only supported to connect to a specific data source. The formua does not work within the DataSource property of the Display form.
As an alternative solution, you could consider create one Display form for each year (e.g. 2017, 2018, 2019, ...) in your Detail screen. Then within your Detail screen, DisplayForm1 connects to UrlList_2018, DisplayForm2 connects to UrlList_2019, ...
After that, set the Visible property of the Display form in your Detail screen to following:
DisplayForm1.Visible:
If( var_JahrTxt = "2018",
true,
false )
DisplayForm2.Visible:
If( var_JahrTxt = "2019", true, false )
...
Please take a try with above solution, check if the issue is solved.
Best regards,
Sorry for the late Reply
Your suggestion was my first impulse.
I tested this and my solution was to reorganize the data.
After the transport to the new data structure I am able to automate the years.
Therefore is the issue solved.
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST
Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.
User | Count |
---|---|
207 | |
188 | |
80 | |
50 | |
38 |
User | Count |
---|---|
305 | |
257 | |
121 | |
73 | |
57 |