Unanswered

Form Validation, lookup and adding to Sharepoint

(0) Share

Report

Posted on by Troyvan

I have a NewForm to enter data in Sharepoint called NZ Jobs Cost

My SharePoint columns (single line of text) are Job#, Client, Job Name, Mgr, ID ( automatically generated ID# for each entry) and a choice column name Ledger.

Choices on Ledger column are Contractors and Subcontractors. I want it to validate that Ledger was selected upon hitting submit and if not, show error on the ledger datacard saying "Please select ledger" so the user can go back to the form and select ledger then hit submit again to add entry to SharePoint.

I also want the app to check if the same Job# and Ledger already exist in SharePoint before adding the new entry. If it does exist, I have a pop up that would say " Job# and ledger already exist". If it doesn't, then it will successfully add row in SharePoint and it will show my pop up - "varSuccess"

I put this formula on the OnSelect property of the Submit Button:

If(IsBlank(DropdownLedger.Selected.Value), Notify("Please select ledger"));
Set(varRecord, LookUp('NZ Jobs Cost', Job# = 'Job#'.Text & Ledger.Value = DropdownLedger.Selected.Value));
If(IsBlank(varRecord.ID), SubmitForm(NewForm), UpdateContext({varSuccess: true}), UpdateContext({varShowX: true}))

Upon hitting submit button, if nothing was selected on DropdownLedger, a notification at the top says Please select ledger - I need this notification to show on Dropdown DataCard itself as error message - How can I do that?

Also, Any record gets submitted even if there's a notification at the top saying Please select ledger. It seems my lookup doesn't work as well as same Job# and ledger gets added multiple times.

Can you please let me know what am I missing? Appreciate your help. Thanks!

Cheers,
Troy

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

WarrenBelz 153,040 Most Valuable Professional on at

Like (0)

Report
Hi @Troyvan ,

Firstly, set the Required property of the Ledger Data Card to true and then change the Text of the Error Message text control to what you need. For the rest of it, you had a number of brackets and commas/semicolons not quite right - try the below - however you also seem to have the Job# Field and Text box names the same - you need to change the name of the Text Box.

With( { wNew: IsBlank( LookUp( 'NZ Jobs Cost', 'Job#' = 'Job No'.Text && Ledger.Value = DropdownLedger.Selected.Value ).ID ) }, If( wNew, SubmitForm(NewForm); UpdateContext({varSuccess: true}), UpdateContext({varShowX: true}) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Troyvan 12 on at

Like (0)

Report

Hi Warren,

Appreciate your response. The form validation worked like a charm. Thanks!
However, I pasted your formula and changed the textbox to "Job No" but I'm getting unexpected character with RED mark after } on ID. Here's what I have:

With(
{
wNew:
IsBlank(
LookUp(
'NZ Jobs Cost',
'Job#' = 'JobNo'.Text &&
Ledger.Value = DropdownLedger.Selected.Value
).ID
},
If(
wNew,
SubmitForm(NewForm);
UpdateContext({varSuccess: true}),
UpdateContext({varShowX: true})
)

What am I missing? Really appreciate your help. Thanks!

Cheers,
Vanessa

Was this reply helpful? Yes No
WarrenBelz 153,040 Most Valuable Professional on at

Like (0)

Report

@Troyvan ,

Just missing a bracket (now fixed) - perils of free-typing code without red lines to guide.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Troyvan 12 on at

Like (0)

Report

Hi Warren,

Please excuse my innocence but I can't pathom where to put the missing bracket, I tried to put it after the curly bracket after ID and it gives me more error:

With(
{
wNew:
IsBlank(
LookUp(
'NZ Jobs Cost',
'Job#' = 'JobNo'.Text &&
Ledger.Value = DropdownLedger.Selected.Value
).ID
}),
If(
wNew,
SubmitForm(NewForm);
UpdateContext({varSuccess: true}),
UpdateContext({varShowX: true})
)

Thanks!
Troy

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,040 Most Valuable Professional on at

Like (1)

Report
@Troyvan ,

I fixed it on the original post, but here it is. Also, you will find this much easier if you parse the code as below as you will see where anything opened needs to be closed.

With( { wNew: IsBlank( LookUp( 'NZ Jobs Cost', 'Job#' = 'JobNo'.Text && Ledger.Value = DropdownLedger.Selected.Value ).ID ) }, If( wNew, SubmitForm(NewForm); UpdateContext({varSuccess: true}), UpdateContext({varShowX: true}) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Troyvan 12 on at

Like (0)

Report

Hi Warren,

Sorry I missed that. Thanks for your tips and really appreciate your help. Your the best!

Cheers,
Troy

Was this reply helpful? Yes No