One SharePoint list contains items that define products and another that contains sales totals: one item in the latter is for each product, for each month and for each year. I thought it would be a no-brainer to create a PowerApps that would generate monthly totals records for each product, but not so much.
The app starts off with the user selecting a month and year to create totals. If they don't as yet exist (I do a ClearCollect with a filter and if the resulting collection is empty, they do not), I want to create the required totals records. What I did was to create a collection of items for each product in the product SP list using default values. So far, so good, that works.
But now I have to Patch the collection of those records to set the product name, the month and the year. In the SP totals list, each of those fields is a choice or lookup column (product is lookup, month and year are choice fields). I cannot even compose a Patch that is error free.
My approach was to do a ForAll for the product list, walking down each product and then try to Patch the fields in the collection of monthly totals records. It sems to me that it actually requires "walking" through both lists: for each item in the product collection, I need to patch a corresponding item in the monthly totals list. I have not figured this out and hope a PowerApps fairy godmother can help.
Even with that solved, I will still have another issue: I need to Patch the Month and Year columns from a variable value, and because they are choice fields, I have not figured out how to craft the code to get that value into the columns in a Patch.
Open to either direct answers on how to do this or suggestions of a dfifferent way of solving the problem.
Thanks in advance!
You are facing one of the common problems with using People, Choice and Lookup type columns in SharePoint. My advice is to avoid them. Convert the Choice fields to Single line of text and use a Dropdown control populated with the items in the field instead. Lookup type columns are more difficult to change. Instead, I create another list and do the lookups from within PowerApps. My last community blog talks about the atomic bomb in SharePoint because IMHO, having an embedded table in a cell violates one of the principles of database design called 1st Normal Form. @Shanescows has a great video about how to avoid using SharePoint lookup fields in the video gallery called
It is possible to convert the two choice fields (month and year) but would require a lot of re-work, but the lookup field for products is crucial since that list changes all the time and is dynamic in that sense.
Converting a Choice type column is easy. In Sharepoint you just choose Single line of text. You won't lose any data but if you want, you could create a new calculated column and pick the choice column from the list and use that column instead. There are some workarounds for these types of SharePoint columns in Powerapps but honestly, I don't have the patience for it.