cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Station1901
Level: Powered On

Edit a record in Sharepoint using a Powerapp

Morning,

I am trying to create a power app which creates a queue of our fleet vehicle numbers which helps our staff decide which vehicle should be issued with a certain type of job.

 

I have a gallery which lists all of our vehicle numbers and shows their job status as "Waiting" - The Gallery only shows the vehicle numbers whose 'Title' is "Waiting"

Once a vehicle has been selected from the gallery the powerapp guides the user to a form which asks them for details of the job.  There is a button on the form that the user presses once the form is complete and the button runs a patch command.  The patch command adds the job details to the Sharpoint list as a new record with a Title of "Complete" (this keeps it out of my gallery) it also creates a new blank record in Sharepoint with the Car number and a Title "Waiting" (so that the new record shows in my gallery).  The problem I have is that the original record is still there in Sharepoint with the Title "Waiting", this means I end up with 2 of the same car number in my gallery.  Is there a way that when the user presses the complete button that the original record's Title result can be changed to "Complete" or anything other than "Waiting"? or even delete the original "Waiting" record.  The powerapp DOES work in the way I want it to apart from this one issue and it is bugging me - just to add I'm still finding my feet with powerapp development.

13 REPLIES 13
Microsoft Employee

Re: Edit a record in Sharepoint using a Powerapp

Hello @Station1901,

When you submit a new job, I would recommend updating the existing record with the job information and setting the status to "Complete" on it, instead of creating a new record.

If however you still want to add a new record and delete the existing record then you will have to use the RemoveIf function. Something like:
RemoveIf(MyDataSource, jobId= Gallery1.selected.jobId)

Station1901
Level: Powered On

Re: Edit a record in Sharepoint using a Powerapp

I would like it if when the user pressed the complete button that exisiting record would update in exactly the way you have suggested but I also need it to create a new 'blank' record for that vehicle so that the user is able to select that vehicle again if they need to - it goes back into the queue as a "waiting" vehicle but the "Complete" record is held in Sharepoint to enable me to build up a history.  The trouble is I have no idea how to do this

Microsoft Employee

Re: Edit a record in Sharepoint using a Powerapp

I understand now why you need to create the record. In this case, you can avoid the duplicates by generating the Queue colleciton out of your datasource rather than adding and removing records by formula. Something like the following:

Set(MyQueueCollection, Filter(MyOrdersDataSource Status="Waiting"))

You can then bind the MyQueueCollection to the gallery and omit the field you don't need.

If you're still looking to manage your Queue collection manually then you can remove items from the collection as following:

Remove(MyQueueCollection , {vehicleNumber:3, Status:"Waiting"})

This will remove a certain reccord (with a defined vehicle number) from the collection. Note that must match the entire record in the "Remove" function in order to delete it from your collection.

Highlighted
TimRohr
Level 10

Re: Edit a record in Sharepoint using a Powerapp

@dinusc has you on the right track... I just wanted to say that it sounds like your data needs to be normalized.

 

Introducing this sort of volatility to your data (close & recreate) is the sort of thing that will eventually come back to bite you.

Vehicles in one list/table/entity. Jobs in another. If there is a N:N relationship, a third list/table/entity of the pairings of a vehicle & job.

Give the Vehicle list a "Checked Out" column that would preclude it showing up in a Gallery until a Job was finished (if that's what you want).

 

On the other hand, if there is a 1:N relationship between vehicles and jobs (the same vehicle can be used in many jobs), then you just would need a field in the job record to store the vehicle ID.

 

I may not have a full grasp of the way you interact with your data, but normalization would simplify it, I think.

KroonOfficeSol
Level 10

Re: Edit a record in Sharepoint using a Powerapp

@Station1901

 

You should rethink your solution. What you need is two list, one for the vehicles your have and one for registration off the usage. 

 

The second list contains off at least the fields: VehicleID (number field), Date, Started (time), Ended (time)

 

Now you do this on the Vehicles gallery.Items

Filter(VehiclesList, !(ID in Filter(RegistrationList, IsBlank(Ended)).VehicleID)) 

// Filter(RegistrationList, IsBlank(Ended)).VehicleID // this part gives back a list off VehicleID's which are currently in use
// !(ID in ...) // this mean ID is not in the list off VehicleID's


You add a gallery with all the not ended registrations. Gallery.OnSelect you do

UpdateContext({SelectedRegistration: ThisItem}) // this will make a variable containing a record.
;EditForm(YourForm)
;Navigate(YourFormScreen, None)

You add a screen with a form. The form.datasource

Form.DataSource = RegistrationList
Form.Item = SelectedRegistration

If you want to create a new registration you goto the same screen only from you Vehicle gallery. Do like

NewForm(YourForm) // set the mode off the form to a new form
;Navigate(FormScreen, None, {PVehicleID: ThisItem.ID}) // go to the formscreen and set a variable to use to set the right vehicle as a default value 

The last thing you want to do is to change the inputtext control on the VehicleID datacard by a combobox. Connect this combobox to the Vehicle Sharepointlist, Set the default selected item with a LookUp() formula and connect the Datacard.Update to the Combobox.Selected.ID.

 

Oké it a little bit more complicated to digest, but like this it will work like a charm.

 

Greatings

 

Paul

 

 

 

Station1901
Level: Powered On

Re: Edit a record in Sharepoint using a Powerapp

Hello Paul,


Thanks for getting back to me.  I have tried your solution but when I create my form with the item SelectedRegistration the power app does not recognise this?

TimRohr
Level 10

Re: Edit a record in Sharepoint using a Powerapp

If you are still having trouble instituting a normalizing schema to your data/app, post back. I can't tell from your last post the state of your work. Smiley Wink

alsabea
Level: Powered On

Re: Edit a record in Sharepoint using a Powerapp

why you make this to much complecated,create two list one with the car names and status and the second list fill with the information of request .in this waz zou dont even ned to create blank item .
best regards

Station1901
Level: Powered On

Re: Edit a record in Sharepoint using a Powerapp

I have gone back to the drawing board and created 2 lists now.  1 with all of the vehicles and 1 which records the jobs that the vehicle does.  I have got it working (Almost).  All i need now is for the vehicle list to be sorted in a gallery by the date they got their last job (which is stored in the second list)