Select with Dropdown Datasource for Gallery

[dobroo]

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?

[Drrickryp]

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. 

[dobroo]

@Drrickryp 

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?

[Drrickryp]

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

[dobroo]

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.

[Drrickryp · Accepted 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. 

[dobroo]

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?

[v-xida-msft · Accepted solution]

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,

[dobroo]

@v-xida-msft 

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.