How to patch a date value from SharePoint list to a collection & how to create choice column in collection

[stavrama]

Hello everyone,

I have created an expenses power app based on Shane's video which btw was great. 

    One of the collection columns i created is a date and i want to scan on change if the date the user entered is within the start of and end of date of expenses.

The start of and end of date values are stored in sharepoint list.

I thought that if i patch those values within the collection , i can use the following:

If(date.SelectedDate>=DateTimeValue(ThisItem.Linestart) && date.SelectedDate<=DateTimeValue(ThisItem.Lineend),Set(WrongDate,false),Set(WrongDate,true));

(***Edited, This is the correct if statement since my first on original post was wrong)

I am having issues though patching the start date and end date values in the collection.

At first i used: Patch(ExpenseCollection,Defaults(ExpenseCollection),{Linestart:vstartdate.SelectedDate}) but whenever i enter the startdate and enddate i get more than one entries in the collection table which makes sense.

Then i tried to use  Patch(ExpenseCollection,ThisItem,{Linestart:vstartdate.SelectedDate}) but i get syntax error.

The 2nd question is more straight forward. I want to create a choice column in collection but i still haven't found any related article. 

Thanks in advance for your help.

[cha_cha]

Hello @stavrama 

For the first question, put your question within the LineEnd

Patch(ExpenseCollection,Defaults(ExpenseCollection),{Linestart:vstartdate.SelectedDate, Lineend: venddate.SelectedDate}) 

If you want to create a choice column in a collection it would be like this

ClearCollect(myCollection,

    {LineStart: Date,

     LineEnd: Date,

     ChoiceColumn: Table(

            {Value: "Choice 1", No: 1},

            {Value: "Choice 2", No: 2},

            {Value: "Choice 3", No: 3}

      )

    }

)

[stavrama]

I don't understand what i have to do on 1st answer :). Start Date and End Date are 2 different SharePoint columns so i added on both of them the patch statement on change. If i create 1 patch on which action i ll use it on the form? Furthermore when it patches it ignores first row on collection.

Regarding the 2nd answer i add that but i receive errors: 

NewForm(Form1);ClearCollect(ExpenseCollection, {Linedate: "", Linecat: Table({Value: "Έξοδα Διατροφής", No: 1},{Value: "Έξοδα Μετάβασης", No: 2},{Value: "Έξοδα Διαμονής", No: 3},{Value: "Λοιπα Έξοδα", No: 4}), Linecattype: "", Linesupp: "", Linerecno: "", Lineexchrate: 1, Linecurtype: "", Lineexpamm: 0,Linestart: "", Lineend: "",ShowSaveButtons: true});Navigate(NewItem,ScreenTransition.Cover);Set(WrongDate,false)

Letters between brackets are Greek.

[stavrama]

Regarding table in col , it is ok. I forgot to update the clear collect values on cancel action.  What will be the new default value though? 

[cha_cha]

You can leave the default blank. But if you want to assign a default, use this code:

LookUp(Linecat,No=1).Value

or

LookUp(Linecat,No=1)

---

✔️	Just in case my response helped you solve your problem, please mark/accept this as a SOLUTION.  This helps community members if they experience a similar issue in the future.

🔗	🕸bistek.space  🐦 @cha_bistek  📺 @BisTekSpace

[stavrama]

Thanks for your help but a few more things arise 🙂

I thought i have figured out how to patch but apparently i was wrong. Can you please help me find the error for the highlighted entry?

ForAll(ExpenseCollection, If(!IsBlank(Linedate),Patch('phtexp-child',Defaults('phtexp-child'), {DateofExpense: Linedate,Category: Linecat ,CategoryType: Linecattype,Supplier: Linesupp,ReceiptNo: Linerecno,ExchangeRate: Lineexchrate,CurrencyType: Linecurtype,ExpenseAmmount: Lineexpamm,MasterID:Form1.LastSubmit.ID}))) 

Category is the SharePoint column i want to patch the choice entry from collection.

I also have issue while patching inside the collection in order to have a new blank row. When i do this the choice option is erased so i guess again the entry is wrong:

Patch(ExpenseCollection,ThisItem, {Linedate:DateValue(date.SelectedDate),Linecat:cat.Selected.Value,Linecattype:cattype.Text,Linesupp:supp.Text,Linerecno:recno.Text,Lineexchrate:Value(exchrate.Text),Linecurtype:curtype.Text,Lineexpamm:Value(expamm.Text),ShowSaveButtons: false});If(EditPressed,false,Collect(ExpenseCollection, {Linedate: "",Linecat: Table({Value: "Έξοδα Διατροφής", No: 1},{Value: "Έξοδα Μετάβασης", No: 2},{Value: "Έξοδα Διαμονής", No: 3},{Value: "Λοιπα Έξοδα", No: 4}), Linecattype: "", Linesupp: "", Linerecno: "", Lineexchrate: 1, Linecurtype: "", Lineexpamm: 0,Linestart: "", Lineend: "", ShowSaveButtons: true}));UpdateContext({EditPressed: false});Set(NeedsSaved, false);Set(WrongDate,false)

Cat is the name of the dropdown field

[stavrama]

SO, i made a few changes and instead of table, i used 2  dropdown fields and i entered on items my choices. 1st Dropdown: ["", "choice1", "choice2", "choice3"] <-- Linecat collection column

On 2nd dropdown i used if statement for lookup: If(cat.Selected.Value = "choice1" , ["2ndchoice1"], cat.Selected.Value = "Choice2" , ["2ndchoice2", "2ndchoice3", "2ndchoice4"]) <--Linecattype collection column

when i save the line in collection i use the following: 

Patch(ExpenseCollection,ThisItem, {Linedate:date.SelectedDate,Linecat: cat.Selected.Value,Linecattype:cattype.Selected.Value,Linesupp:supp.Text,Linerecno:recno.Text,Lineexchrate:Value(exchrate.Text),Linecurtype:curtype.Selected.Value,Lineexpamm:Value(expamm.Text),ShowSaveButtons:false});If(EditPressed,false,Collect(ExpenseCollection, {Linedate: "",Linecat: "", Linecattype: "", Linesupp: "", Linerecno: "", Lineexchrate: 1, Linecurtype: "", Lineexpamm: 0,Linestart: "", Lineend: "", ShowSaveButtons: true}));UpdateContext({EditPressed: false});Set(NeedsSaved, false);Set(WrongDate,false)

And when i submit the form i use the following to patch the entries on the sharepoint list:

ForAll(ExpenseCollection, If(!IsBlank(Linedate),Patch('phtexp-child',Defaults('phtexp-child'), {DateofExpense: Linedate,Category: cat.Selected ,CategoryType: cattype.Selected,Supplier: Linesupp,ReceiptNo: Linerecno,ExchangeRate: Lineexchrate,CurrencyType: curtype.Selected,ExpenseAmmount: Lineexpamm,MasterID:Form1.LastSubmit.ID})))

My problem is that on save, the 2nd  dropdown choice is deleted.

The 2nd problem is that i just realized i have made mistake on sharepoint patch. I have used cat.selected where cat is the name of the dropdown field. Linecat is the name of the collection column but whatever i tried i get an error when i am using linecat.

[stavrama]

I spam sorry but i figured out the last question. The correct value is Category: {Value: Linecat}

After this 1st problem on my previous reply was solved also. So the only thing i still need help is my first question on my topic 🙂

[stavrama · Accepted solution]

I found a solution for the first question also.

So since i wanted to write on my first collection record, i created a new coll column: DateID: "1" with fixed value of 1 and i add on the 2 date time fields the following patch on change action: 

Patch(ExpenseCollection,LookUp(ExpenseCollection, DateID="1" ) ,{Linestart:vstartdate.SelectedDate})

Patch(ExpenseCollection,LookUp(ExpenseCollection, DateID="1" ) ,{Lineend:venddate.SelectedDate})

and Voilà!

[cha_cha]

Hello @stavrama ,

glad you figured things out.