Submit button is erroring that a value must be provided for item

[JemmaD]

Hi there,

Nearly there with my app - the last hurdle is that it won't patch my data to the receiving table.

So I have two SQL tables -

The first is called Powerapps.CRM_ACCOUNT and holds with a distinct list of ACCOUNT_NUMBER and ACCOUNT_NAME. I pulled this into my app and it's used in a vertical gallery for users to search by account number or account name. 

Filter('Powerapps.CRM_Account',ACCOUNT_SEARCH.Text in CRM_ACCOUNT_NAME || ACCOUNT_SEARCH.Text in CRM_ACCOUNT_NUMBER)

The second SQL table is called Powerapps.SCHEDULE_OF_SERVICES and holds CRM_ACCOUNT_NUMBER as well as 8 other fields, all of which can be populated in my app. There's a mix of drop downs, text input, radio button and checkbox. 

The key between the two SQL tables is called ACCOUNT_ID which is just an index I guess, and is not anywhere in the app itself.

My submit button should look for changes to any of the fields and then push the data into Powerapps.SCHEDULE_OF_SERVICES by matching the selected ACCOUNT_NUMBER to the SQL CRM_ACCOUNT_NUMBER. 

Here is the OnStart code for my app:

ClearCollect(
    CollectUpdate,
    ShowColumns(
        Table(Defaults('Powerapps.CRM_SCHEDULE_OF_SERVICES')),
        "ACCOUNT_ID",
        "CRM_ACCOUNT_NUMBER",
        "ANNUAL_FEE",
        "SCHEDULE_YEAR",
        "SCHEDULE_INCLUDED",
        "SERVICE_WEIGHTING",
        "FREQUENCY_AGREED",
        "VOLUME_AGREED",
        "SCHEDULE_ACHIEVED",
        "LOADDATE",
        "USER"
    );
    Clear(CollectUpdate)
);

I'm not sure if I should be including ACCOUNT_ID or CRM_ACCOUNT_NUMBER in this code because they are already populated in the SQL table, and will not be updated.

Here is the OnSelect code for my Submit button:

ForAll(
    CollectUpdate,
    Patch(
        'Powerapps.CRM_SCHEDULE_OF_SERVICES',
        LookUp('Powerapps.CRM_SCHEDULE_OF_SERVICES', (Text(CRM_ACCOUNT_NUMBER) = BROWSE_ACCOUNT.Selected.BROWSE_ACCOUNTNUMBER.Text)),
        {
            CRM_ACCOUNT_NUMBER: BROWSE_ACCOUNTNUMBER,
            ANNUAL_FEE: ANNUAL_FEE,
            SCHEDULE_YEAR: SCHEDULE_YEAR,
            SCHEDULE_INCLUDED: IS_INCLUDED,
            SERVICE_WEIGHTING: NEW_SERVICEWEIGHTING,
            FREQUENCY_AGREED: NEW_FREQUENCYAGREED,
            VOLUME_AGREED: NEW_VOLUMEAGREED,
            SCHEDULE_ACHIEVED: NEW_SCHEDULEACHIEVED,
            LOADDATE: Now(),
            USER: User().Email
             } ) );
             Notify("Submission successful", NotificationType.Success)

As you can see i'm asking it to check ACCOUNT_NUMBER selected from my search gallery equals CRM_ACCOUNT_NUMBER in SQL as the lookup.

ACCOUNT_NUMBER in the gallery and is just a label which says ThisItem.ACCOUNT_NUMBER.

Can anyone help me get this working? I know i've done something wrong but I don't know what!

[gcmfaizan]

@JemmaD ,

can you update your for all with this:

ForAll(
    CollectUpdate As ThisRec,
    Patch(
        'Powerapps.CRM_SCHEDULE_OF_SERVICES',
        LookUp('Powerapps.CRM_SCHEDULE_OF_SERVICES', CRM_ACCOUNT_NUMBER = BROWSE_ACCOUNTNUMBER.Text),
        {
            CRM_ACCOUNT_NUMBER: ThisRec.BROWSE_ACCOUNTNUMBER.Text,
            ANNUAL_FEE: ThisRec.ANNUAL_FEE,
            SCHEDULE_YEAR: ThisRec.SCHEDULE_YEAR,
            SCHEDULE_INCLUDED: ThisRec.IS_INCLUDED,
            SERVICE_WEIGHTING: ThisRec.NEW_SERVICEWEIGHTING,
            FREQUENCY_AGREED: ThisRec.NEW_FREQUENCYAGREED,
            VOLUME_AGREED: ThisRec.NEW_VOLUMEAGREED,
            SCHEDULE_ACHIEVED: ThisRec.NEW_SCHEDULEACHIEVED,
            LOADDATE: Now(),
            USER: User().Email
        }
    )
);
Notify("Submission successful", NotificationType.Success)

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Thanks!

[JemmaD]

@gcmfaizan thanks for this, it's now erroring that ANNUAL_FEE does not match the expected type 'Number' (this is a number type field)

And it says ThisRec isn't recognised. This is my revised code:

ForAll(
    CollectUpdate as ThisRec,
    Patch(
        'Powerapps.CRM_SCHEDULE_OF_SERVICES',
        LookUp('Powerapps.CRM_SCHEDULE_OF_SERVICES', (Text(CRM_ACCOUNT_NUMBER) = BROWSE_ACCOUNT.Selected.BROWSE_ACCOUNTNUMBER.Text)),
        {
            CRM_ACCOUNT_NUMBER: ThisRec.BROWSE_ACCOUNTNUMBER.Text,
            ANNUAL_FEE: ThisRec.ANNUAL_FEE,
            SCHEDULE_YEAR: ThisRec.SCHEDULE_YEAR,
            SCHEDULE_INCLUDED: ThisRec.IS_INCLUDED,
            SERVICE_WEIGHTING: ThisRec.NEW_SERVICEWEIGHTING,
            FREQUENCY_AGREED: ThisRec.NEW_FREQUENCYAGREED,
            VOLUME_AGREED: ThisRec.NEW_VOLUMEAGREED,
            SCHEDULE_ACHIEVED: ThisRec.NEW_SCHEDULEACHIEVED,
            LOADDATE: Now(),
            USER: User().Email
             } ) );
             Notify("Submission successful", NotificationType.Success)

[gcmfaizan]

@JemmaD,

can you rape ANNUAL_FEE in the value function:
like:

Value(ThisRec.ANNUAL_FEE)

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Thanks!

[JemmaD]

@gcmfaizan it's still upset, it started saying it didn't recognise any of the values of the other fields so I updated them all with a Value() and it still doesn't like it, it says:

Here is my latest syntax:

ForAll(
    CollectUpdate as ThisRec,
    Patch(
        'Powerapps.CRM_SCHEDULE_OF_SERVICES',
        LookUp('Powerapps.CRM_SCHEDULE_OF_SERVICES', CRM_ACCOUNT_NUMBER = BROWSE_ACCOUNT.Selected.BROWSE_ACCOUNTNUMBER.Text),
        {
            CRM_ACCOUNT_NUMBER: Value(ThisRec.BROWSE_ACCOUNTNUMBER.Text),
            ANNUAL_FEE: Value(ThisRec.ANNUAL_FEE.Value),
            SCHEDULE_YEAR: Value(ThisRec.SCHEDULE_YEAR.Selected.Value),
            SCHEDULE_INCLUDED: Value(ThisRec.IS_INCLUDED.Value),
            SERVICE_WEIGHTING: Value(ThisRec.NEW_SERVICEWEIGHTING.Selected.Value),
            FREQUENCY_AGREED: Value(ThisRec.NEW_FREQUENCYAGREED.Selected.Value),
            VOLUME_AGREED: Value(ThisRec.NEW_VOLUMEAGREED.Value),
            SCHEDULE_ACHIEVED: Value(ThisRec.NEW_SCHEDULEACHIEVED.Value),
            LOADDATE: Now(),
            USER: User().Email
             } ) );
             Notify("Submission successful", NotificationType.Success)

I'm not sure how to reference each of the fields - I only know that drop downs like Selected.Value

Here is what each field type is:

BROWSE_ACCOUNT_NUMBER - type Label (text)

ANNUAL_FEE - type Text input (number)

SCHEDULE_YEAR - type Drown down (number)

IS_INCLUDED - type Checkbox

NEW_SERVICEWEIGHTING - type Drop down (number)

NEW_FREQUENCYAGREED - type Drop down (text)

NEW_VOLUMEAGREED - type Text input (number)

NEW_SCHEDULEACHIEVED - type Radio

[gcmfaizan]

@JemmaD ,

just use value function there where column type is Number, 
can you try this formula,

ForAll(
    CollectUpdate as ThisRec,
    Patch(
        'Powerapps.CRM_SCHEDULE_OF_SERVICES',
        LookUp('Powerapps.CRM_SCHEDULE_OF_SERVICES', CRM_ACCOUNT_NUMBER = BROWSE_ACCOUNT.Selected.BROWSE_ACCOUNTNUMBER.Text),
        {
            CRM_ACCOUNT_NUMBER: Text(ThisRec.BROWSE_ACCOUNTNUMBER.Text),
            ANNUAL_FEE: Value(ThisRec.ANNUAL_FEE.Text),
            SCHEDULE_YEAR: Value(ThisRec.SCHEDULE_YEAR.Selected.Value),
            SCHEDULE_INCLUDED: Value(ThisRec.IS_INCLUDED.Value),
            SERVICE_WEIGHTING: Value(ThisRec.NEW_SERVICEWEIGHTING.Selected.Value),
            FREQUENCY_AGREED: Value(ThisRec.NEW_FREQUENCYAGREED.Selected.Value),
            VOLUME_AGREED: Value(ThisRec.NEW_VOLUMEAGREED.Text),
            SCHEDULE_ACHIEVED: Value(ThisRec.NEW_SCHEDULEACHIEVED.Value),
            LOADDATE: Now(),
            USER: User().Email
        }
    )
);
Notify("Submission successful", NotificationType.Success)

If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Thanks!

[JemmaD]

@gcmfaizan changing that entry to .Text did not fix the code:

The errors are :

Invalid use of '.' against the rows from CRM_ACCOUNT_NUMBER down to SCHEDULE_ACHIEVED

Expected operator such as +, * or & (but doesn't tell me where)

Name isn't valid. 'ThisRec' isn't recognised

The function 'ForAll' has some invalid arguments

The function 'Text' has some invalid arguments

The function 'Value' has some invalid arguments