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

[cv2qm]

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 AppsSQL table

Please let me know how I can do this?

Thanks!

[Drrickryp]

@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]

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.  

[cv2qm]

I am trying to get this.

STUDENT_SYSTEM_ID	PRIMARY_NAME	AWARD	OFFER_AMOUNT
1061454	A	ER0vck	98765
1061454	A	FDR465	7989
1076679	B	DVC7658	98765
1099148	C	VCR6578	10000
1099148	C	DR9870	909
1121819	D	DR5674	1000

[Pstork1]

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

[cv2qm]

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

[Pstork1]

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

[v-xida-msft · Accepted solution]

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:

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,

[cv2qm]

@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]

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,