Answered

New form submission - Duplicate error when it's not a duplicate

(0) Share

Report

Posted on by Eveneggerud

Hello.

I've set up the following code on the "OnSelect" property of my submit new form. I have my data linked to an excel file.

If(
EditForm1_1.Mode = FormMode.New And CountRows(
Filter(
Tabell1,
Upper(Artikkelnummer) = Upper(DataCardValue8_1.Text)
)
) = 0,
SubmitForm(EditForm1_1),
Notify(
"En artikkel med dette artikkelnummeret finnes allerede.",
NotificationType.Error,
5000))

In the excel file I have EPP002908SP on one row. But when I tried addon a new one with EPP002908SP-B I got the error saying it's already registered in the excel file.

But if I have one named Test, and try adding Test-B, I'm allowed to submit the new form.

Any tips? I thought the new EPP002908SP-B was unique and ok to post to the excel file?

Please let me know if I need to give you more information about my app.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Hi @Eveneggerud ,
Excel is not the best data source for complex queries, but try this

With( { wDup: LookUp( Tabell1, StartsWith( DataCardValue8_1.Text, Artikkelnummer ) ).Artikkelnummer }, If( EditForm1_1.Mode = FormMode.New && IsBlank(wDup), SubmitForm(EditForm1_1), Notify( "En artikkel med dette artikkelnummeret finnes allerede.", NotificationType.Error, 5000 ) ) )

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
Eveneggerud 14 on at

Like (0)

Report

Hi Warren.

I'm just building a small app for myself to use, so I thought excel would be sufficient.

I tried adding EPP002908SP-B, but I'm still not allowed to submit. I tried EPP002908SB for fun, and that was allowed. I've attached the excel file, maybe I've formatted the rows incorrectly?

Lagerbeholdning_K...

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

Like (0)

Report
Hi @Eveneggerud ,

I do not use Excel, so cannot troubleshoot it, but both pieces of code should work - it is just that Upper() is not Delegable, so I used StartsWith() which is not case sensitive. If you put this in a Label

LookUp( Tabell1, StartsWith( DataCardValue8_1.Text, Artikkelnummer ) ).Artikkelnummer

do you get any result ? If you get nothing, logic tells you it is blank and therefore the code should work. If you get a result, you need to look at your data.

Was this reply helpful? Yes No
Eveneggerud 14 on at

Like (0)

Report

Hi @WarrenBelz

Interesting. I've added a text label as you suggested, and typing EPP002908SP-B in the datacard returns EPP002908SP in the text label using the lookup formula.

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

Like (0)

Report

@Eveneggerud ,

As I mentioned Excel is best avoided if you have access to SharePoint - it makes no sense that EPP002908SP starts with EPP002908SP-B (the other way around is true)

Was this reply helpful? Yes No
Verified answer

Eveneggerud 14 on at

Like (0)

Report
Hello @WarrenBelz

I tried creating an app from a new sharepoint list, and the formula with the "StartsWith" acted identical. I did however manage to figure out a solution using the following change.

With( { wDup: LookUp( Tabell1, DataCardValue8_1.Text = Artikkelnummer ).Artikkelnummer }, If( EditForm1_1.Mode = FormMode.New && IsBlank(wDup), SubmitForm(EditForm1_1), Notify( "En artikkel med dette artikkelnummeret finnes allerede.", NotificationType.Error, 5000 ) ) )

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

Like (1)

Report

@Eveneggerud ,

Yes, equals will work if not case sensitive

Was this reply helpful? Yes No