How to make the repeating table in power apps?

[pkumar]

Hello,

I want to make the repeating table in Power apps. I am using Gallery and trying to insert the data in SQL database I have the two tables one is the main table which is storing other form fields values and other one is for repeating table. I made the relation primary key and foreign key relation between the table. I want to insert the data in repeating table sql database. But data is not get inserted into the database. If I am opening the screen in each time it showing many-2 blank rows.

I am using below expression to submit the form where IncidentReport is collection and [dbo].[IncidentReport] is the table name.

SubmitForm(EditForm);Collect(IncidentReport,'[dbo].[IncidentReport]');

Below are the four fields to create the new row on the screen and binded the fields with the collection table.

Patch(IncidentReport, ThisItem,{ContractName: Contract_Name_2.Text, Position: Contract_Position_2.Text, CompanyName: Company_Name_2.Text,ContactNumber: ContactNumber_2.Text});Collect(IncidentReport,{ContractName: "",Position: "",CompanyName: "",ContactNumber: ""});

On form Suucess function I am using below code:

ForAll('[dbo].[IncidentReport]',Patch(IncidentReport,Defaults(IncidentReport),{ContractName:ContractName,Position:Position,CompanyName:CompanyName,ContactNumber:ContactNumber}));

On the same form I want to use images in repeating table. And the same screen can have multiple repeating table.  Can any one please help out on the repeating table?

I followed the below Shane tutorial 

https://youtu.be/xgznk4XlPCo

[v-xida-msft]

Hi @pkumar ,

Could you please share a bit more about the formula you typed within the Items property of the Gallery?

Does your Gallery connect to your repeating table SQL Table?

Based on the formula you provided, I could only see single one SQL Table data source, is the '[dbo].[IncidentReport]' your repeating table SQL Table? Do you want to use Patch function to add rows into your repeating table SQL Table?

According to the needs that you mentioned, I assume that the '[dbo].[IncidentReport]' represents your repeating table SQL Table, is it true?

I think there is something issue with the ForAll formula you provided within the OnSuccess property of the Edit form, please consider modify your formula as below:

ForAll(
         Filter(Gallery1.AllItems, !IsBlank(Contract_Name_2.Text)),             // Gallery1 represents the Gallery where you design your repeating table
         Patch(
               '[dbo].[IncidentReport]',
               Defaults('[dbo].[IncidentReport]'), 
               {
                ContractName:  Contract_Name_2.Text,
                Position: Contract_Position_2.Text,
                CompanyName: Company_Name_2.Text,
                ContactNumber: ContactNumber_2.Text
               }
         )
);

or

ForAll(
        IncidentReport,
        If(
           !IsBlank(ContractName),
           Patch(
                '[dbo].[IncidentReport]',
                 Defaults('[dbo].[IncidentReport]'),
                 {
                  ContractName: IncidentReport[@ContractName],
                  Position: IncidentReport[@Position],
                  CompanyName: IncidentReport[@CompanyName],
                  ContactNumber: IncidentReport[@ContactNumber]
                 }
           )
       )
);

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[pkumar]

Hi @v-xida-msft  

In the Items Property I have selected the collection ContractInformation.

Gallery connected repeating table SQL Table. 

Yes want to add a patch function to add rows in repeating table SQL table. 

I have the two table '[dbo].[IncidentReport]'(ID is primary) and '[dbo].[ContractInformation]'(Incident_ID is foreign key). [dbo].[ContractInformation]represents repeating table SQL Table.

With the help of primary key and foreign key I want to pull the repeating table data from SQL [dbo].[ContractInformation] table  on the power apps screen. 

Can you please confirm how I can use the both table in power app screen?

Also I want multiple repeating table on the same screen. Repeating table can have images and attachments and fields.

[v-xida-msft]

Hi @pkumar ,

Do you want to retrieve the records from your [dbo].[ContractInformation] table based on primary key from '[dbo].[IncidentReport]' table and foreign key in your [dbo].[ContractInformation] table?

Have you taken a try with the solution I provided?

Please consider take a try with the following workaround:

Set the OnVisible property of the current screen to following:

Collect(ContractInformation, '[dbo].[ContractInformation]')

Set the Items property of the Gallery to following (I assume that you add a Gallery control in your app to list all available '[dbo].[IncidentReport]' table records😞

Filter(
       ContractInformation,
       If(
          EditForm1.Mode = FormMode.Edit,
          Incident_ID = BrowserGallery.Selected.ID,
          true
       )
)

Set the OnSelect property of the "Save" icon in the Gallery to following:

Patch(
       ContractInformation, 
       ThisItem,
       {
         ContractName: Contract_Name_2.Text, 
         Position: Contract_Position_2.Text, 
         CompanyName: Company_Name_2.Text,
         ContactNumber: ContactNumber_2.Text
       }
);
Collect(
        ContractInformation,
        {
           ContractName: "", 
           Position: "", 
           CompanyName: "",
           ContactNumber: ""
        }
);

Set the OnSuccess property of the Edit form, please consider modify your formula as below:

ForAll(
        ContractInformation,
        If(
           !IsBlank(ContractName),
           Patch(
                '[dbo].[ContractInformation]',
                 Defaults('[dbo].[ContractInformation]'),
                 {
                  ContractName: ContractInformation[@ContractName],
                  Position: ContractInformation[@Position],
                  CompanyName: ContractInformation[@CompanyName],
                  ContactNumber: ContractInformation[@ContactNumber]
                 }
           )
       )
);
ClearCollect(ContractInformation, '[dbo].[ContractInformation]')

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[pkumar]

Hi @v-xida-msft ,

Yes, I want to retrieve the records from your [dbo].[ContractInformation] table based on primary key from '[dbo].[IncidentReport]' table and foreign key in your [dbo].[ContractInformation] table.

Yes, I tried previous solution.

OnSuccess

It always asking ID field is required.

Can you please confirm why it is asking the ID is required?

On screen Edit mode the the repeating table is showing hidden and it only showing if I am creating a new. On new form data is not get inserted into the SQL [dbo].[ContractInformation] repeating table.

[v-xida-msft]

Hi @pkumar ,

Actually, it is a normal behavior within your '[dbo].[ContractInformation]' table. If you have specified the Incident_ID field as Primary Key in your '[dbo].[ContractInformation]' table, it would be acted as Required field, it means you must provide a proper value for it.

Set the OnVisible property of the current screen to following:

Clear(ContractInformation);
If(
   EditForm1.Mode = FormMode.New,
   Collect(
           ContractInformation,
           {
            ContractName: "", 
            Position: "", 
            CompanyName: "",
            ContactNumber: ""
           }
  ),
  ForAll(
         '[dbo].[ContractInformation]',
          Collect(
                  ContractInformation,
                  {
                    ContractName: '[dbo].[ContractInformation]'[@ContractName],
                    Position: '[dbo].[ContractInformation]'[@Position],
                    CompanyName: '[dbo].[ContractInformation]'[@CompanyName],
                    ContactNumber: '[dbo].[ContractInformation]'[@ContactNumber]
                  }
          )
  )
)

Set the Items property of the Gallery (Gallery1) to following:

ContractInformation

On your side, please consider modify the formula within the OnSuccess property of the Edit form to following:

ForAll(
         Filter(Gallery1.AllItems, !IsBlank(Contract_Name_2.Text)),             // Gallery1 represents the Gallery where you design your repeating table
         If(
             IsBlank(LookUp('[dbo].[ContractInformation]', ContractName = Contract_Name_2.Text && Position = Contract_Position_2.Text && CompanyName = Company_Name_2.Text && ContactNumber = ContactNumber_2.Text)),
             Patch(
                  '[dbo].[ContractInformation]',
                  Defaults('[dbo].[ContractInformation]'), 
                 {
                  Incident_ID: EditForm1.LastSubmit.ID,   // Modify formula here
                  ContractName:  Contract_Name_2.Text,
                  Position: Contract_Position_2.Text,
                  CompanyName: Company_Name_2.Text,
                  ContactNumber: ContactNumber_2.Text
                 }  
            )
         )
);

Please consider take a try with above solution, check if the issue is solved.

Best regards,

[pkumar]

Hi, 

'[dbo].IncidentReport' table has ID column which is primary key. Incident_ID is the foreign key in the '[dbo].[ContractInformation]' table, also a ID column is the primary key in this table. 

OnVisible

Code is not working.

Data inserted in the  SQL database is not showing a proper selection on the inserted rows. If one row is inserted in the SQL  '[dbo].[ContractInformation]' table then it is showing on all the submitted records. The repeating tables rows data is not showing on the  basis of primary key.

Can you please advise for this?

[v-xida-msft]

Hi @pkumar ,

Have you set "Auto-Increment" (Identity(1,1)) for the ID column in your '[dbo].[ContractInformation]' table?

Please consider take a try with the following workaround:

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

NewForm(EditForm1);
Navigate(EditScreen);
Clear(ContractInformation);
Collect(
           ContractInformation,
           {
            ID: "",
            ContractName: "", 
            Position: "", 
            CompanyName: "",
            ContactNumber: "",
            Incident_ID: ""
           }
)

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

EditForm(EditForm1);
Navigate(EditScreen);
Clear(ContractInformation);
ForAll(
         '[dbo].[ContractInformation]',
          Collect(
                  ContractInformation,
                  {
                    ID: '[dbo].[ContractInformation]'[@ID],
                    ContractName: '[dbo].[ContractInformation]'[@ContractName],
                    Position: '[dbo].[ContractInformation]'[@Position],
                    CompanyName: '[dbo].[ContractInformation]'[@CompanyName],
                    ContactNumber: '[dbo].[ContractInformation]'[@ContactNumber],
                    Incident_ID: '[dbo].[ContractInformation]'[@Incident_ID]
                  }
          )
 )

Within your Edit screen, set the Items property of the Repeating Table Gallery to following (filter ContractInfomation records based on foreign key😞

If(
   EditForm1.Mode = FormMode.New,
   ContractInformation,
   Filter(ContractInformation, Incident_ID = IncidentReportGallery.Selected.ID)
)

Note: I assume that you use a Gallery to list all available Incident report records.

Set the OnSuccess property of the Edit form to following:

ForAll(
         Filter(RepeatingTableGalery.AllItems, !IsBlank(Contract_Name_2.Text)),
         If(
             IsBlank(LookUp('[dbo].[ContractInformation]', ContractName = Contract_Name_2.Text && Position = Contract_Position_2.Text && CompanyName = Company_Name_2.Text && ContactNumber = ContactNumber_2.Text)),
             Patch(
                  '[dbo].[ContractInformation]',
                  Defaults('[dbo].[ContractInformation]'), 
                 {
                  Incident_ID: EditForm1.LastSubmit.ID,
                  ContractName:  Contract_Name_2.Text,
                  Position: Contract_Position_2.Text,
                  CompanyName: Company_Name_2.Text,
                  ContactNumber: ContactNumber_2.Text
                 }  
            )
         )
);

If you do not set the "Auto-Increment" property for the "ID" column in your '[dbo].[ContractInformation]' table, please consider modify above formula as below:

ForAll(
         Filter(RepeatingTableGalery.AllItems, !IsBlank(Contract_Name_2.Text)),
         If(
             IsBlank(LookUp('[dbo].[ContractInformation]', ContractName = Contract_Name_2.Text && Position = Contract_Position_2.Text && CompanyName = Company_Name_2.Text && ContactNumber = ContactNumber_2.Text)),
             Patch(
                  '[dbo].[ContractInformation]',
                  Defaults('[dbo].[ContractInformation]'), 
                 {
                  ID: Last('[dbo].[ContractInformation]').ID + 1, // Add formula here
                  Incident_ID: EditForm1.LastSubmit.ID,
                  ContractName:  Contract_Name_2.Text,
                  Position: Contract_Position_2.Text,
                  CompanyName: Company_Name_2.Text,
                  ContactNumber: ContactNumber_2.Text
                 }  
            )
         )
);

Best regards,

[pkumar]

Hello @v-xida-msft 

OnSelect property of New button is not working. 

NewForm(EditForm1);
Navigate(EditScreen);
Clear(ContractInformation);
Collect(
           ContractInformation,
           {
            ID: "",
            ContractName: "", 
            Position: "", 
            CompanyName: "",
            ContactNumber: "",
            Incident_ID: ""
           }
)

When I am adding the above formula. It giving me below error:

Incompatible type . The item you're trying to put into a collection has a type that's not compatible with the collection The function has invalid arguments.

[v-xida-msft]

Hi @pkumar ,

Please consider modify formula within the OnSelect property of the "New" button to following:

NewForm(EditForm1);
Navigate(EditScreen);
Clear(ContractInformation);
ForAll(
          Filter('[dbo].[ContractInformation]', IsBlank(ID)),  //  modify formula here
          Collect(
                  ContractInformation,
                  {
                    ID: '[dbo].[ContractInformation]'[@ID],
                    ContractName: '[dbo].[ContractInformation]'[@ContractName],
                    Position: '[dbo].[ContractInformation]'[@Position],
                    CompanyName: '[dbo].[ContractInformation]'[@CompanyName],
                    ContactNumber: '[dbo].[ContractInformation]'[@ContactNumber],
                    Incident_ID: '[dbo].[ContractInformation]'[@Incident_ID]
                  }
          )
 )

or

NewForm(EditForm1);
Navigate(EditScreen);
Clear(ContractInformation);
ForAll(
          Filter('[dbo].[ContractInformation]', ID = -1),  //  modify formula here
          Collect(
                  ContractInformation,
                  {
                    ID: '[dbo].[ContractInformation]'[@ID],
                    ContractName: '[dbo].[ContractInformation]'[@ContractName],
                    Position: '[dbo].[ContractInformation]'[@Position],
                    CompanyName: '[dbo].[ContractInformation]'[@CompanyName],
                    ContactNumber: '[dbo].[ContractInformation]'[@ContactNumber],
                    Incident_ID: '[dbo].[ContractInformation]'[@Incident_ID]
                  }
          )
 )

Best regards,