Changing the Item field of a Table with a ComboBox in PowerApps

[Anonymous]

Hello Powerapps Community!

I'm currently making an app that requires to be able to change a Datatable Source using a Combobox, I  tried using Update context or a simple global variable. Unfortunatelly it dosen't change it since it's not a "Table" but a "Text"value.

Would any of you have an idea on how to do this, or at least some directions?

Thank you in advance for your time!

[tommyly · Accepted solution]

DataTables will display a collection (aka Table) in table format. So you would have to plug in the name of a collection or DataSource into the dataTable's Items property.

So if you have two DataSources "customers1" and "customer2", then you can create a comboBox (with multi-select turned off) or a listBox that has these values. You can do this one of two ways (that I know of):

1: Put something like this into your screen's OnVisible event to create a collection and then in your comboBox's Items property, plug in "myComboList" (without the quotes).

Clearcollect(myComboList,
  Table(
    {ListItem: "customers1"},
    {ListItem: "customers2"},
  )
)

2: If you don't want to have to deal with an in-memory collection, in the comboBox's Items list, simply plug in:

Table(
  {ListItem: "customers1"},
  {ListItem: "customers2"},
)

In the DataTable's Items property, you can put "yourComboList.Selected.ListItem" (without the quotes). The problem I've found with dataTables is that it, like a dataForm, expects a static set of columns. That is, customers1 has a column "Full Name" and customers2 has two columns "First Name" and "Last Name", the dataTable will orient itself to the initial DataSet (let's say for eaxmple customers1) and throw a fit when you try to dynamically set it to customers2.

I hope others will chime in (to my benefit as well), but the only way I can see doing this is by throwing in some design magic. You would add columns where needed and then use ShowColumns to filter out columns that were different.:

ClearCollect(myCustomCollection1,
  ShowColumns(customers1
    "FullName", "address", ...
  )
);



ClearCollect(myCustomCollection2,
  ShowColumns(
    AddColumns(customers2,
      "fullName", firstName & ", " & lastName,
      ...
    ),
    "FullName", "address", ...
)

DataSet customers1 uses its original/native name of "fullName", while customers2 has a column added  for "fullName" and then a ShowColumns command filters out the no-longer-used firstName and lastName.

Now, using the example above regarding the comboBoxes, you can set your comboBox's choices to include "myCustomCollection1" and "myCustomCollection2". Now when you select the custom collections, the table will update dynamically using the standardized column names. I hope that helps.

[Anonymous]

Thanks for your reply, I forgot to add in a couple of details:

-I'm using Excel Tables and these are the ones i want shown in the Datatable

-The ComboBox is using another Table which contains all the names of the different Tables I want shown in the Datatable.

From what I understood I need either to create a collection or setup the values manually in the ComboBox Items. The problem here is you declare them as "Text" values so the DataTable's Items cannot process it.

Basically what I'm looking for is to manipulate a "Table" value. I tried using "yourComboList.SelectedItems.ListItem" the issue here is that it will only show me the name of the selected item in the DataTable. The second thing i need to know (assuming that the Item of the DataTable is set correctly) is to add names of columns in the DataTable (like you would select them with the graphical interface) I can't see where it is being set.

After trying your suggestions they unfortunatelly do not work for me...

Thank you for your help!

[tommyly]

I think I may be misunderstanding what you are attempting to do. If you have a few screenshots, that may help clarify things.

[Anonymous]

On this first screenshot you can see that I set the Items of my ComboBox to be "listeliste" which is an excel table containing the names of other excel tables that i want to see on the Datatable at the right of the Combobox

 

On the Second Screenshot you can see that I'm setting the Items as "ComboBox1.SelectedItems.Nom_Liste" (Nom_Liste being the name of the only column in listeliste). In theory it should get the selected ITEMS and therefor use them as "Table" values.

In the third screenshot you can see what results i expect when using the combobox to choose "listeunite" which contains various units and being able to view then in the DataTable.

Unfortunatelly this does not work, the Datatable will only show me the name of "listeunite" in the DataTable (and this only after choosing to show "Nom_Liste" in the properties of the Datatable.

I hope this will help you understand where help is needed,

Thank you in advance!

[tommyly]

You are right. I just tested it and it is passing the literal "listeunite" instead of the actual dataSource (or collection) listeunite.

I thought of 2 possible ways to address this:

1. First Option: On ComboBox1.OnChange event, create an if statement that collects a standardized collection:

If(ComboBox1.Selected.Nom_Liste="xyz",
  ClearCollect(myCustomTable, xyz),

If(ComboBox1.Selected.Nom_Liste="abc",
  ClearCollect(myCustomTable, abc)

))

*I noticed that you used the ComboBox1.SelectedItems.Nom_Liste. The SelectedItems infers that you are using Allow multiple selections. I'm presuming that you mean to allow only 1 selection, so you would switch this option to Off and use the property ComboBox1.Selected.Nom_Liste instead.

And then you would set DataTable1.Items to myCustomTable. Now, it should resolve as the actual collection and not the literal text.

2. Second Option: On DataTable1.Items, you can do something like this:

If(ComboBox1.Selected.Nom_Liste="xyz",
  xyz,

If(ComboBox1.Selected.Nom_Liste="abc",
  abc

))

This approach would be very similar, but instead of creating the collection as an event on the comboBox, you are creating the collection from the dataTable.

I encourage you to keep looking for other solutions, this is how I would solve this issue (my preference would be option #1, so that I have some liberty to manipulate the collection (e.g. Collect to append more data, etc.). I hope this helps.

[Anonymous]

I tested out option 1 and it works great! The only thing that needs to be done is to actually be able to select de the field displayed in the DataTable:

We can see that the DataTable's source is properly set but the field "Unite" which shows the data is not selected and therefore the data is not visible.

Another concern I have is by putting the Table in a Collection if we chage/add to the collection will it also update the Table in the Excel file?

I had already tried using something like your second option but it is simply not feasable:

On the second screenshot you can see on the right it takes both of the Tables as the source. As I understand it, in the Items field of the DataTable (this works perfectly on other Items from other Objects but not on DataTables), it will look through to find a "Table" type value regardless of conditions.

[tommyly · Accepted solution]

Based on your picture, it looks like your myCustomTable collection (which presumably used a ClearCollect command to create) collected a single column called "Unite". You can double-check your collection by clicking on View > Collection > myCustomTable. Make sure the collection was collected correctly. You may also want to double-check to make sure that your Excel dataSource is correct as well--that is, having more than one column.

The problem I've found with dataTables is that--as far as I know--you cannot dynamically change its column scheme during run-time. I've had to select the columns during edit-mode using a standardized format and then use code and temporary collections to update the data. In some ways, you using the myCustomTable is doing that already. What you would further do is:

1. Define your format in Screen1.OnVisible or Screen1.OnStart:

   ClearCollect(myCustomTable, Table({FullName: Blank(), Address: Blank(), Age: 0})

   There is no need to worry about the dummy-data since when you ClearCollect later on, it will clear out this data. The purpose of this is to create the collection's columns and datatype scheme.
2. Run your program once to instantiate/create the myCustomTable collection.
3. Break out of your program into edit-mode and then add myCustomTable as the source for DataTable1 (e.g. DataTable1.Items).
   1. Select the columns (e.g. Unite) while in edit-mode. I wish there was a dynamic way of selecting the columns; if there is, I haven't figured it out yet.
   2. Once you figure out why your other columns are not showing up, you can select those as well
4. Now, when your other Excel sources have an odd/different column name, you will have to manipulate your instruction in your comboBox. For example, let's assume datasource xyz uses "FirstName" and "FullName" columns instead of "FullName" and dataSource abc uses "Street", "City", "State", "ZIP" instead of "Address":

   If(ComboBox1.Selected.Nom_Liste="xyz",
     ClearCollect(myCustomTable,
       ShowColumns(  
         AddColumns(xyz, "FullName", FirstName & ", " & LastName"),
         FullName, Address, Age
       )
     )
   
   If(ComboBox1.Selected.Nom_Liste="abc",
     ClearCollect(myCustomTable,
       ShowColumns(  
         AddColumns(abc, "Address", Street & ", " & City & " " & State & " " & ZIP),
         FullName, Address, Age
       )
     )

I suppose if you want to show different column schemes, you could use two different tables and assign them different column schemes (during edit-mode) and then use code to turn one visible and the other invisible. The user-experience would assume it was the same table.

And to answer your question, creating a collection (e.g. ClearCollect, Collect, Clear) and removing/clearing it does not affect your datasource (e.g. your Excel spreadsheet). It creates a temporary, in-memory object to hold the data and all subsequent references to the temporary collection will affect only the temporary collection. So using AddColumns and ShowColumns will not add columns or erase columns on your spreadsheet, if that's what you are asking. 

[Anonymous]

My "listeunite" contains only one column "Unite" so this part is normal.

The fact that a collection has no affect on the Excel table is problematic for me as the goal is to:

Select a Table with ComboBox --> Visualise data in Datatable  --> Select line to edit/remove or add a new one.

The proplem I had was that i couldn't dynamically set up something like this

Select Table with Combobox --> Datatable shows Table with all Columns

So we're coming back to the core problem which is being able to dynamically change  a  Data Table source using Excel Tables with the goal to Edit/remove or create records in the Excel Tables using a form which will also change it's source depending on the ComboBox.

I hope this time you can see what the goal is.

Thanks for your help

[tommyly · Accepted solution]

Let's take a step back then. It sounds like you may have to rethink your design, but let me cover some basics that I've learned so far:

From my perspective, you would only group datasources (e.g. in a datatable) in PowerApps if they are part of the same class of information (e.g. data all related to people... or data all related to products... etc.). If they are different classes of information (e.g. one about people and another about products) then you ought to consider different tables (can make one visible while the others are invisible; overlapping them will make them look like the same table) or even different screens.

To update your dataSource, you can use the Patch, Update, UpdateIf, Remove, RemoveIf commands while referencing the datasource. If you program, then a ClearCollect'ed collection is not a reference to an object. It's more like a clone of the object, but you can still use them to affect your true, datasource (i.e. real changes to your spreadsheet). For example, If you end up using a temporary collection (e.g. for the purpose of adding columns, joining data from another datasouce, etc.), instead of patching temporaryListeUniteCollection, you would patch listeunite. And then you would invoke a ClearCollect to refresh your temporary collection.

I hope that helps. I feel like I haven't been able to give you a solution for your specific request, but hopefully you have some avenues to look at. My personal opinion and experience is that 9 times out of 10, when it gets too complicated, I'm usually overthinking it and there's probably an easier solution if I rethink the design.