How to convert spreadsheet to Power App connected to SharePoint list

(0) Share

Report

Posted on by SPS-DEV-22

516

I have an excel spreadsheet that been asked to make it a power app connected to SharePoint list.

I was wondering how would I handle the fact that in a spreadsheet there is a Check in and Check out process that can happen lets say 20 times (multiple days/possible same day) on the same Item #.. There are additional fields that would be filled on besides the 3 shown. but was wondering how do I setup the SharePoint List columns?

Do I create Date & Time | Action 1 |Action 2 | and then add columns 1 thru 20? and then on Power App make it a check box (conditional button when clicked the data fields for the new days appear on the form) which would then bring in the next date and time with the fields to fill in additional days for the item

Example:

Date & Time2 ..... Date and Time20 |Action1_2 .... Action1_20 | Action2_2 .... Action2_20 |

Item # USDA001

Date and Time	Action 1	Action 2
1/1/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/2/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/3/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/4/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf

or is there a different way to achieve the data entry of the Action items multiple times and get the data saved for each day .

thanks for any advice and or ideas to solve the issue is appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

@SPS-DEV-22

Here is a sample asset management database that uses three tables. It may have more than you need but it is a start.

Was this reply helpful? Yes No
SPS-DEV-22 516 on at

Like (0)

Report
Copy link

Link copied!

I'm using a SharePoint list for the data. thanks for the SQL data idea. that is why I was asking about the power app and SharePoint list having the # of columns matching depending on how many times the item was checked in and checked out for the various days

Was this reply helpful? Yes No
jaredbidlow 256 on at

Like (0)

Report
Copy link

Link copied!

Split your data by the columns and import that into a Sharepoint list (or paste in the Grid view data). This usually can build a relationship around an equipment ID/Asset ID in a simple manner. A thing that could be useful is the (if your interested in switching over to Dataverse through a Dataflow, or to keep it local in Power Query in Excel) transofrmation acheived by unpivoting data, to bring about your desired setup. It may seem like a hack but if you simply unpivot those three columns you have there, and then filter the data for the specific attribute to save as a new table. You would have four tables, Assets, Dates, Action 1, Action 2.

Was this reply helpful? Yes No
SPS-DEV-22 516 on at

Like (0)

Report
Copy link

Link copied!

I'm not using the equipment ID/Asset # item, my issue is at the very top with the Action 1 Action 2 Item and that there are several days where that action can take place for the same item #.
I'm just trying to figure out how the powerapps form/sharepoint list can handle the check in /check out of the item multiple times over a course of 20 days and save that data each time

Was this reply helpful? Yes No
jaredbidlow 256 on at

Like (0)

Report
Copy link

Link copied!

But you would ideally want the data in an expanded format, where the index number is attached to all the necessary datetimes or actions. Only then can we use the lookup formula to gather related information.

Was this reply helpful? Yes No

SPS-DEV-22

516 on at

Like (0)

Report

Copy link

Link copied!

So, what you are saying is to create 18 columns?

Item # USDA001

Date and Time	Action 1	Action 2
1/1/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/2/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/3/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf
1/4/1900	Removed from Box	Returned to Shelf
	Returned to Box	Remove from Shelf

these are the columns that repeat, and I was told possible up to 18 times in a month.

this can repeat 18 times. where each time it repeats it is a new Date and Time and then the columns are filled out for that day, by the input person.
There are other columns in the SharePoint list it will have lots of columns but those above will repeat until the item closes out. Not sure if this is going to require 2 SharePoint lists, or if I can get away with 1 SharePoint list holding all the data.

I would prefer to use 1 SharePoint list with a Power App Attached, where there is the new, edit, display forms for the item #.

Any help or direction would be great.

Was this reply helpful? Yes No

jaredbidlow

256 on at

Like (0)

Report

Copy link

Link copied!

I was saying to do a power query unpivot to translate into a filtering capability in powerapps.
Item # Date and Time Attribute Value

USDA001	01/03/22 0:00	Action 2	Returned to Shelf
USDA001	01/03/22 0:00	Action 1	Returned to Box
USDA001	01/03/22 0:00	Action 2	Remove from Shelf
USDA001	01/04/22 0:00	Action 1	Removed from Box
USDA001	01/04/22 0:00	Action 2	Returned to Shelf
USDA001	01/04/22 0:00	Action 1	Returned to Box
USDA001	01/04/22 0:00	Action 2	Remove from Shelf

Was this reply helpful? Yes No

SPS-DEV-22 516 on at

Like (0)

Report
Copy link

Link copied!

I'm trying to move away from the Excel File. As that is what the client wants to do.

So, I don't think this will work, power query unpivot to translate into a filtering capability in powerapps, plus I never done a power query unpivot

Was this reply helpful? Yes No
jaredbidlow 256 on at

Like (0)

Report
Copy link

Link copied!

The least you could do is "fill down" manually in Excel for each Item # in a new column. So you still have to deal with the variable number of columns, all the way up to Action 18. I would separate the action table in a new list. Copy and paste it or separate in different Excel sheets for sharepoint import. This will normalize the data a little bit, but manually.

Was this reply helpful? Yes No