Errors within code being used to save data from my audit power app and store in excel.

[cwilsonLUR]

Hello, I am in the process of designing an audit app for my employer, I'm new to power apps and have been completing Microsoft and Udacity training programmes to teach myself how to create the app. I am also following a video on YouTube too see how another person designed their audit app and implementing some of his techniques within my app. I have made multiple tables on excel for the use of cascading drobox's, retrieving data (audit info, questions etc.) and tables for the answers to be stored in once an audit is complete. I have got to the part where I am entering a submit button and having some errors within my coding for the 'onSelect'. I believe its to do with the app being able to retrieve the selected value from the drop box and store it in the collection/tables. I will include a copy and paste of my code along with a screenshot. Any help, would be appreciated. Thanks 

Set(IDAuditVar,Patch(AuditInfo,Defaults(AuditInfo),
{

Author:AuthorName.Text,
Company:Text(CompanyDropbox.Selected.Value),
Audit:Text(AuditNameDropbox.Selected.Value),
Location:Text(LocationDropbox.Selected.Value),
Created:NowVar
}).ID);

ForAll('ManagerH&SauditGallery'.AllItems,
UpdateIf(ManagerHScol,ID=Value(IDGal.Text),
{
AuditID:IDAuditVar,
Answer:AnswerOptions.Selected.Value,
Comment:CommentBox.Text,
NumberOfImages:0
}));
Collect(ManagerHSauditQuestion,ManagerHScol)
;
ForAll(PictureCol,
Patch(ManagerHSauditQuestion,Defaults(ManagerHSauditQuestion),
{
AuditAnswerId.Question,
Picture:Iamge,
AuditId:IDAuditVar
}));
ClearCollect(ManagerHScol,ManagerHSauditQuestion);
Clear(PictureCol);Reset(LocationDropbox);Reset(CompanyDropbox);Reset(AuditNameDropbox) 

[v-jefferni · Accepted solution]

Hi @cwilsonLUR ,

For error 1:

You can select ControlName.Selected then below the formula bar there would be an arrow appears. Click the arrow then you will be able to see the table structure (columns and values). Also, you can check the table that are used for Items property of the two Dropdowns, find the column name then replace Value in the formula. Besides, if values are not of type number, Text function is not needed.

Set(
    IDAuditVar,
    Patch(
        AuditInfo,
        Defaults(AuditInfo),
        {
            Author:AuthorName.Text,
            Company:CompanyDropbox.Selected.Company,
            Audit:AuditNameDropbox.Selected.Audit,
            Location:LocationDropbox.Selected.Location,
            Created:NowVar
        }
    ).ID
);

For error 2:

Use below formula instead, UpdateIf is use to bulk update records that match the criteria:

ForAll(
    'ManagerH&SauditGallery'.AllItems,
    Update(
        ManagerHScol,
        LookUp(ManagerHScol,ID=Value(IDGal.Text)),
        {
            AuditID:IDAuditVar,
            Answer:AnswerOptions.Selected.Value,
            Comment:CommentBox.Text,
            NumberOfImages:0
        }
    )
);

For error 3, you have missed a column name within the Patch function:

ForAll(
    PictureCol,
    Patch(
        ManagerHSauditQuestion,
        Defaults(ManagerHSauditQuestion),
        {
            Question:AuditAnswerId.Question, //assume "Question" is the column name
            Picture:Iamge,
            AuditId:IDAuditVar
        }
    )
);

In addition, you will be able to see detailed error messages when moving the cursor over those red waves, which will be very helpful for you to know what's the problem.

Best regards,

[cwilsonLUR]

@v-jefferni Thanks for quick reply and quick solution, much appreciated, have a nice day!

[cwilsonLUR]

Hi @v-jefferni,

I'm still having a few issues with my code, I'm going to send you some screenshots of my code for the submit button and drop boxes as well as the tables I have and if you have any recommendations it would be appreciated. I'm using one table for relating the droboxes, an identical questions and answers tables which is linked to the ManagaerHScol and two tables to save the audit info and images. Thanks 

[v-jefferni · Accepted solution]

Hi @cwilsonLUR ,

For the error in the first formula, try below:

Set(
    IDAuditVar,
    Patch(
        AuditInfo,
        Defaults(AuditInfo),
        {
            Author:AuthorName.Text,
            Company:CompanyDropbox.Selected.Result,
            Audit:AuditNameDropbox.Selected.Audit,
            Location:LocationDropbox.Selected.Location,
            Created:NowVar
        }
    ).ID
);

The reason is Items property of Company Combo box is using the Distinct function, so the returned table will only have Result column. You can find suggested column names after inputting a dot after some properties, e.g. "CompanyComboBox.Selected." will have Result coming up in the suggestions list.

Best regards,

[cwilsonLUR]

Perfect @v-jefferni  That worked, Thanks. I'm still getting an error message for the update function. I have checked the question and answer data tables and believe everything is the correct data type in excel. I think the code is correct but the I have messed up something elsewhere? Thanks

[v-jefferni · Accepted solution]

Hi @cwilsonLUR ,

Could you please check where this collection is generated? It seems to be generated with different data types.

Best regards,

[cwilsonLUR]

So these are the two tables I'm using to retrieve the questions in my apps and store the answers to the individual audits. They are exactly the same. The data type I've selected for each column is  ID=Number , AuditID=Number , Question=Text , Answer=Text , Comment=Text and NumberOfImages=Number. From what I have researched this seems to be correct, as the answer is coming from a radio button I was unsure of that data type but assumed text. Both are identical. Any ideas? Thanks 

[v-jefferni]

Hi @cwilsonLUR ,

ManagerHScol is a collection based on your screenshot. Please find where it is being created from the beginning using formula Collect or ClearCollect. I think it's on a button OnSelect or a screen OnVisible or even App's OnStart.

Best regards,

[v-jefferni]

Edit.