cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cv2qm
Frequent Visitor

Patch for one or two records in SQL for same key value

Hello Community,

I am new to Power Apps and community.
I have a Following requirements.

1. Insert into SQL table if user enters new record by Combo Box selection name, either one record or two records for same Primary key with different values.

2. Update the record if user wants to update the record either one or both by Combo Box selection name.

I am trying with Patch function, I am able to do it for one record. Don't know how i can do it for two records for both update and insert. 

 

Patch(
    'Table Name',Defaults( Table Name ),
    {
        PRIMARY_NAME: ComboBox1.Selected.PRIMARY_NAME,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Value(TextInput1_2.Text)
    },
    {
        PRIMARY_NAME: ComboBox1.Selected.PRIMARY_NAME,
        AWARD: Text(TextInput1_4.Text),
        OFFER_AMOUNT: Value(TextInput1_5.Text)
    }
)

 

 

Power AppsPower AppsSQL tableSQL table

Please let me know how I can do this?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @cv2qm ,

Do you want to add or update two records in your SQL Table via press the "Submit" button once time?

 

Regarding the needs that you mentioned, I agree with @Pstork1 's thought almost. If you want to update records in your SQL Table, you need to use a Unique Type column as a Primary Key, e.g. a Auto-Increment type column (identify(1,1)).

 

Actually, it is not necessary to add four individual Text Input box controls in your canvas app to collect data entry, instead, I think the Gallery control could achieve your needs.

You could consider add a Gallery control in your app, then add two Text Input Box inside it. The configuration may look like below:

9.JPG

10.JPG

Set the OnStart property of App to following:

ClearCollect(Table1, {Id:1, PRIMARY_NAME: "", AWARD: "", OFFER_AMOUNT: ""});Clear(Table1);
ClearCollect(NewEntry, Defaults(Table1), Defaults(Table1))

On your side, you should type:

ClearCollect(NewEntry, Defaults('Table Name'), Defaults('Table Name'))

 

Add a Gallery, set the Items property to following:

If(
    ComboBox1.Selected.Value in Table1.PRIMARY_NAME,
    Filter(Table1, PRIMARY_NAME = ComboBox1.Selected.Value),
    NewEntry
)

On your side, you should type:

If(
    ComboBox1.Selected.PRIMARY_NAME in 'Table Name'.PRIMARY_NAME,
    Filter(Table1, PRIMARY_NAME = ComboBox1.Selected.PRIMARY_NAME),
    NewEntry
)

Within the Gallery, add two Text Input boxes, one for AWARD, another one for OFFER_AMOUNT. Set the Default property of AWARD TextInput box (TextInput1) to following:

ThisItem.AWARD

set the Default property of the OFFER_AMOUNT TextInput box (TextInput2) to following:

ThisItem.OFFER_AMOUNT


Set the OnSelect property of the "Submit" button to following:

If(
    ComboBox1.Selected.Value in Table1.PRIMARY_NAME,
    ForAll(
          RenameColumns(Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)), "Id", "Id1"),
          Patch(
                Table1,
                LookUp(Table1, PRIMARY_NAME = ComboBox1.Selected.Value && Id = Id1),
                {
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
          )
    ),
    ForAll(
           Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)),
           Patch(
                 Table1,
                 Defaults(Table1),
                {
                  Id: CountRows(Table1) + 1,
                  PRIMARY_NAME: ComboBox1.Selected.Value,
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
           )
    )
)

On your side, you may need to type the following formula:

If(
    ComboBox1.Selected.PRIMARY_NAME in 'Table Name'.PRIMARY_NAME,
    ForAll(
          RenameColumns(Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)), "Id", "Id1"),
          Patch(
                'Table Name',
                LookUp('Table Name', PRIMARY_NAME = ComboBox1.Selected.PRIMARY_NAME && Id = Id1),
                {
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
          )
    ),
    ForAll(
           Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)),
           Patch(
                 'Table Name',
                 Defaults('Table Name'),
                {
                  PRIMARY_NAME: ComboBox1.Selected.PRIMARY_NAME,
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
           )
    )
)

Please try above solution, then check if the issue is solved.

 

I have also attached my sample app below, please refer to it, then check if it could help in your scenario.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Drrickryp
Super User
Super User

@cv2qm 

Two records for the same primary key sounds like a one to many relationship.  If there are only two allowed, you could use Concatenate 

Patch(
    'Table Name',Defaults( Table Name ),
    {
        PRIMARY_NAME: ComboBox1.Selected.PRIMARY_NAME,
        AWARD: Text(TextInput1_3.Text),
        OFFER_AMOUNT: Concatenate(Value(TextInput1_2.Text),", ",Value(TextInput1_5.Text))
    }
   
)
Pstork1
Dual Super User
Dual Super User

You can't have two records in SQL with the same primary key.  The primary key needs to be unique.  As @Drrickryp said you can have two records in another table with the same foreign key value that both refer to a record with a primary key.  



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365EduCon in Chicago on Sept. 26-30 where I'll be presenting on the Power Platform!
cv2qm
Frequent Visitor

I am trying to get this.

 

STUDENT_SYSTEM_IDPRIMARY_NAMEAWARDOFFER_AMOUNT
1061454AER0vck98765
1061454AFDR4657989
1076679BDVC765898765
1099148CVCR657810000
1099148CDR9870909
1121819DDR56741000
Pstork1
Dual Super User
Dual Super User

That may be doable, but neither Student_System_ID or Primary_Name are Primary Keys in SQL.  They can't be since they aren't unique.  To update a SQL database it must have a field that is an autogenerated Identity.  That is the primary Key.  What you looking to do is an UpdateIF().  Take a look at this BLOG post about batch updates of SQL in Power Apps



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365EduCon in Chicago on Sept. 26-30 where I'll be presenting on the Power Platform!
cv2qm
Frequent Visitor

@Pstork1  Thanks for reply, I can change table with unique identifier identify column. 
Can you share blog for UpadteIf. 

 

 

Pstork1
Dual Super User
Dual Super User

I'm sorry.  I thought I pasted the link in, but I guess I hit Enter too fast.

 

http://powerappsguide.com/blog/post/how-powerapps-performs-bulk-updates-in-sql



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Join me at 365EduCon in Chicago on Sept. 26-30 where I'll be presenting on the Power Platform!
v-xida-msft
Community Support
Community Support

Hi @cv2qm ,

Do you want to add or update two records in your SQL Table via press the "Submit" button once time?

 

Regarding the needs that you mentioned, I agree with @Pstork1 's thought almost. If you want to update records in your SQL Table, you need to use a Unique Type column as a Primary Key, e.g. a Auto-Increment type column (identify(1,1)).

 

Actually, it is not necessary to add four individual Text Input box controls in your canvas app to collect data entry, instead, I think the Gallery control could achieve your needs.

You could consider add a Gallery control in your app, then add two Text Input Box inside it. The configuration may look like below:

9.JPG

10.JPG

Set the OnStart property of App to following:

ClearCollect(Table1, {Id:1, PRIMARY_NAME: "", AWARD: "", OFFER_AMOUNT: ""});Clear(Table1);
ClearCollect(NewEntry, Defaults(Table1), Defaults(Table1))

On your side, you should type:

ClearCollect(NewEntry, Defaults('Table Name'), Defaults('Table Name'))

 

Add a Gallery, set the Items property to following:

If(
    ComboBox1.Selected.Value in Table1.PRIMARY_NAME,
    Filter(Table1, PRIMARY_NAME = ComboBox1.Selected.Value),
    NewEntry
)

On your side, you should type:

If(
    ComboBox1.Selected.PRIMARY_NAME in 'Table Name'.PRIMARY_NAME,
    Filter(Table1, PRIMARY_NAME = ComboBox1.Selected.PRIMARY_NAME),
    NewEntry
)

Within the Gallery, add two Text Input boxes, one for AWARD, another one for OFFER_AMOUNT. Set the Default property of AWARD TextInput box (TextInput1) to following:

ThisItem.AWARD

set the Default property of the OFFER_AMOUNT TextInput box (TextInput2) to following:

ThisItem.OFFER_AMOUNT


Set the OnSelect property of the "Submit" button to following:

If(
    ComboBox1.Selected.Value in Table1.PRIMARY_NAME,
    ForAll(
          RenameColumns(Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)), "Id", "Id1"),
          Patch(
                Table1,
                LookUp(Table1, PRIMARY_NAME = ComboBox1.Selected.Value && Id = Id1),
                {
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
          )
    ),
    ForAll(
           Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)),
           Patch(
                 Table1,
                 Defaults(Table1),
                {
                  Id: CountRows(Table1) + 1,
                  PRIMARY_NAME: ComboBox1.Selected.Value,
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
           )
    )
)

On your side, you may need to type the following formula:

If(
    ComboBox1.Selected.PRIMARY_NAME in 'Table Name'.PRIMARY_NAME,
    ForAll(
          RenameColumns(Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)), "Id", "Id1"),
          Patch(
                'Table Name',
                LookUp('Table Name', PRIMARY_NAME = ComboBox1.Selected.PRIMARY_NAME && Id = Id1),
                {
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
          )
    ),
    ForAll(
           Filter(Gallery1.AllItems, !IsBlank(TextInput1.Text) && !IsBlank(TextInput2.Text)),
           Patch(
                 'Table Name',
                 Defaults('Table Name'),
                {
                  PRIMARY_NAME: ComboBox1.Selected.PRIMARY_NAME,
                  AWARD: TextInput1.Text,
                  OFFER_AMOUNT:TextInput2.Text
                }
           )
    )
)

Please try above solution, then check if the issue is solved.

 

I have also attached my sample app below, please refer to it, then check if it could help in your scenario.

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-xida-msft, thank you great work.

 

I modified based on your suggestions and inputs, but when the user wants to add another Award number and amount for Primary name there is no second entry for that person.
I hope it is taking as when Primary name has only one record in SQL, then it showing one entry. if Primary name has only two records in SQL, then it showing two entries. if Primary name has no award in SQL, then it showing one entry in APP. 

 

But in my case, I will get blank Awards and Amounts in table. I will get Primary name's and their Id's. 
User wants to enter Award and Amount for Primary name on TextInput's if nothing is allocated. 
If user want to Award a second Award and Amount for Primary name then another TextInput's has to show-up. 
Defaults has to show, because if he wants to update or change. 

 

Thanks!

I really appreciate for your replies. 

 

v-xida-msft
Community Support
Community Support

Hi @cv2qm ,

For the solution I provided above, it would always generate two entries for the selected Primary Name which is not existed in your SQL Table. If there are records related to this selected Primary Name, these corresponding records from the SQL Table would be shown up in the Gallery.

 

Please make sure you have re-loaded your canvas app to make the OnStart property of App fire as expected. Please share a screenshot where your issue is when trying my solution.

 

In addition, if you want to add the entry in this Gallery dynamically, please check and see if the following video would help in your scenario:

https://www.youtube.com/watch?v=xgznk4XlPCo

https://www.youtube.com/watch?v=DylxsXIUyDc

 

Regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,858)