Patch Function - Required field error that makes no sense

[nickellis74]

Hi all, I've seen a similar thread that was resolved by it turning out to be a different field that was required but missing. I've been through and audited and every required field is being passed through here and it's still refusing to work. Any ideas welcome...

Basically I'm building an agricultural app to count the number of crates picked in a day. Because the users are literally in a field (as in a patch of land, not the IT use of 'Field') I need to have full offline capability, so every time something is saved it goes to a collection, then checks for connectivity. If it finds it then it patches to Dataverse, if not it saves locally.

The data model is basically two tables on a One-to-Many relationship - One Picking Job has Many Picked Crates. Picking Job is itself essentially a joining table of relationships to various other tables - Farm, Field, Product, Variety, Supervisor - plus a date and a 'Mobile GUID' field that I'm using to make sure the Dataverse table stays aligned with the local collection and saved data.

To cut a long story short, whether I reference the date field using the label 'Date' or the technical name 'wbc_date' I get the same error:

And I completely agree - the Date value is required. But it is supplied! Why is it throwing a 'Field is required' error on a field which I am populating?

In the initial population of the collection it is populated using Today(). Here's the whole thing:

//Set the current Picking Job and Picker
UpdateContext(
    {
        PickingJob: First(
            Filter(
                'Picking Jobs',
                'Picking Job' = GalleryJobSelector.Selected.MobileGUID
            )
        ),
        Picker: First(
            Filter(
                offlinePickers,
                'Picker Number' = txtCratePicker.Text
            )
        )
    }
);
//Calculate the total weight of the entered record
UpdateContext({TotalWeight: (((PickingJob.'Punnets per Crate' * PickingJob.'Punnet Weight' * Value(txtCrates.Text)) + (PickingJob.'Punnet Weight' * Value(txtPunnets.Text))) / 1000)});
//Collect the record into the collection
Collect(
    colPickedCrates,
    {
        Crates: Value(txtCrates.Text),
        Date: Today(),
        Picker: Picker,
        'Picking Job': PickingJob,
        Product: PickingJob.Product,
        Punnets: Value(txtPunnets.Text),
        'Punnets per Crate': PickingJob.'Punnets per Crate',
        Supervisor: CurrentWorker,
        'Total Weight': TotalWeight,
        Variety: PickingJob.Variety,
        'Weight per Punnet': PickingJob.'Punnet Weight',
        Field: PickingJob.Field,
        Saved: false,
        Update: false,
        MobileGUID: Text(GUID())
    }
);
//If there is an available internet connection
If(
    Connection.Connected,
//For every row in the collection marked as not saved
    ForAll(
        Filter(
            colPickedCrates,
            Saved = false
        ),
        //Check that the picking job exists, if not then create it
        If(
            CountRows(
                Filter(
                    'Picking Jobs',
                    'Mobile GUID' = MobileGUID
                )
            ) = 0,
            Patch(
                'Picking Jobs',
                Defaults('Picking Jobs'),
                {
                    Crop: PickingJob.Crop,
                    Date: PickingJob.Date,
                    Farm: PickingJob.Farm,
                    Field: PickingJob.Field,
                    'Mobile GUID': PickingJob.MobileGUID,
                    Product: PickingJob.Product,
                    'Punnets per Crate': PickingJob.'Punnets per Crate',
                    'Punnet Weight': PickingJob.'Punnet Weight',
                    Supervisor: PickingJob.Supervisor,
                    Variety: PickingJob.Variety
                }
            )
        );
        
//Patch the record to the Picked Crates table
Patch(
            'Picked Crates',
            Defaults('Picked Crates'),
            {
                Crates: Crates,
                Date: Date,
                Field: Field,
                'Mobile GUID': MobileGUID,
                Picker: Picker,
                'Picking Job': First(
                    Filter(
                        'Picking Jobs',
                        'Mobile GUID' = MobileGUID
                    )
                ),
                Product: Product,
                'Punnets per Crate': 'Punnets per Crate',
                Punnets: Punnets,
                Supervisor: Supervisor,
                'Total Weight': 'Total Weight',
                Variety: Variety,
                'Weight per Punnet': 'Weight per Punnet'
            }
        );
        
//Update the collection record to show as saved
Patch(
            colPickedCrates,
            First(
                Filter(
                    colPickedCrates,
                    MobileGUID = ThisRecord.MobileGUID
                )
            ),
            {Saved: true}
        );
    ),
//If no connection, save the entire collection locally
    SaveData(
        colPickedCrates,
        "PickedCratesInLocalStorage"
    );
    
);
//Reset the field values
Set(
    varCrates,
    0
);
Reset(txtCratePicker);
Reset(txtCrates);
Reset(txtPunnets);

Any and all help gratefully received!

Thanks

Nick

[Pstork1]

Two things I would suggest trying that might fix the issue.

1) I would use the internal name for the date field in all your functions, including the collection.  'Date' is a reserved word and may be causing an issue.

2) When patching back to the data source I would put the Date value inside a text() function to format it as an ISON 8601 date.  The collection may not be saving it in the right format.

[nickellis74]

Thanks for that. I'll try both of those. Frustratingly it is now doing exactly the same behaviour with Weight per Punnet

So that's just a decimal number, 'Weight per Punnet' is obviously not a reserved name... it's exasperating!

[Pstork1]

Its not a reserved word, but it could still be considered the wrong data type.  Try surrounding it with a Value() function to make sure its considered a number and not text.

[nickellis74]

I found it. It's because I'm passing it in from a variable which is itself blank. The challenge now is to work out why that variable is blank despite the fact that it should get populated on screen load and there is no formula setting it to Blank() anywhere!

Thanks for your help, much appreciated.

[nickellis74]

Hi @Pstork1

Firstly, sorry for not hitting the Kudos buttons earlier, very poor form and now corrected.

I was going to start a new thread but I think this is a variant of the same issue. I'm getting "Missing column. Your formula is missing a column 'Owner' with a type of 'Polymorphic'." despite the fact that the column is in fact there and since it's always the same person at the moment it's hard-coded so it's not passing a blank!

Going a step further, if I add .User on the end (just to confirm it is finding an object definition at least) I get this:

So it's complaining that there is no Polymorphic column called Owner, but if I change the setting on the Owner column it complains that it's no longer polymorphic. That suggests that it in fact knows that the Owner column is there and is being passed a Polymorphic value. To say I am confused would be an understatement!

Any ideas? I do appreciate your help. 

[Pstork1]

I'll need to play around with this a bit.  But the issue seems to revolve around the schema of the record you are trying to use as the owner.  The documentation on patching the owner column shows a slightly different  method.  Take a look at the following and I'll try to reproduce your issue.  

Understand record references and polymorphic lookups in canvas apps - Power Apps | Microsoft Docs

[nickellis74]

I will read that, thank you. But - I actually hit on it just after I finished typing.

The issue is not with the Owner of the record in question. If you look above you'll see a reference to 'Picking Job' which as I said is One end of the One-to-Many relationship. It appears PowerApps had successfully identified that in certain circumstances it was possible to patch the picking records before the picking job had been patched. So it was the owner of the Picking Job, not of the Picking Record, that it was upset by.

Solution - put a check for the Picking Job record at the top, patch in if not found, then use a Lookup to retrieve the record. But now it's refusing to compare two GUIDs! Apparently it never ends! 😁

[Pstork1]

I think it might be a syntax issue.  I finally found an example and it uses the following syntax to set the owner.  Note the single quotes around 'Email'

Patch(datasource, defaults(datasource),
{   .....other fields,
     owner: LookUp(Users, 'Email'="johndoe@domain.com")
}
}

[nickellis74]

Thanks! I would accept that as a solution, but I think we're a long way off the original topic now so it might be misleading. I really appreciate the support.