Network Error while using Patch function: Field is Required (In Forall over gallery)

[ToubleShoot123]

Hello everybody,

I've stumbled over a really odd problem when trying to create an editable table using containers and a gallery.

In this editable table I have a dropdown box. It gets its values from a SQL Table that basically serves as an ENUM.

So for the settings of the Dropdown:

Items = FieldTypes //(this is a connected table)

Default = LookUp(FieldTypes; ID = ThisItem.FieldType).Name
//The gallery this dropdown is in contains a Column called FieldType that is an integer/ID

There is also a button, that once activated does the following:

ForAll(
        gal_Table_Columns.AllItems;
        Patch(
            ReferencedObjectColumns;
            ThisRecord;
            {
                ...
                FieldType: drp_Table_FieldType.Selected.ID;
                ... //A similar snippet works fine for TextInputs or Toggles
            }
        )
)

 This oddly enough throws the error in the title. Hovering over the Code snippet say the value is null.

Even stranger things happen when I add the following snippet to the DropDown OnChange Method:

OnChange = Patch(ReferencedObjectColumns; ThisItem; { FieldType: drp_Table_FieldType.Selected.ID })

Not only does it properly Patch the Data, if you change the Field but it also makes the error of the "SaveAll Button" disappear even if you don't change anything in the data.

Maybe somebody has a clue in this regard, as this seems like quite unexpected behaviour to me. 

[v-jefferni]

Hi @ToubleShoot123 ,

In Patch function, the second parameter should be one of the records that belongs to the table of first parameter. In you case, ThisItem is apparently from Gallery.AllItems, which is not the same data source as Patch function. 

I assume Items of Gallery is TableA, source table to Patch is TableB, to update existing items in Table B based on Gallery:

ForAll(Gallery.AllItems, Patch(TableB, LookUp(TableB, criter that find the record in Table B), {TabelBColumn1:..., TabelBColumn2:..., TabelBColumn3:..., TabelBColumn4:...}))

to create new entries in Table B:

ForAll(Gallery.AllItems, Patch(TableB, Defaults(TableB), {TabelBColumn1:..., TabelBColumn2:..., TabelBColumn3:..., TabelBColumn4:...}))

Best regards,

[ToubleShoot123]

Hi @v-jefferni 

I actually want to Patch TableA (the one where the items of the gallery come from)

This works great for all non-dropdown fields.

However for the dropdown we had to take the Items from another table (since the SQL Server TableA does not support Enums and we needed a secondary table for the categories.

In other places the way I've proceeded works fine this way:

- Dropdowns in a Form Submit just fine by giving the correct formula to the Update Property of the Datacard.

- Dropdowns in a Galery (without a datacard) patch just fine when using OnChange.

It just does not work in the ForAll Patch context. 

Basically we don't want to Patch TableB. We want to Patch Table A. Table B's only purpose is to provide the valid categories for the dropdown (linked to Table A by an ID).

[v-jefferni]

Hi @ToubleShoot123 ,

So, the requirement is to bulk update items in the Gallery? Please try:

ForAll(
        gal_Table_Columns.AllItems As GA;
        Patch(
            ReferencedObjectColumns;
            LookUp(ReferencedObjectColumns; ID = GA.ID);
            {
                ...
                FieldType: GA.drp_Table_FieldType.Selected.ID;
            }
        )
)

Best regards,

[ToubleShoot123]

Hi @v-jefferni 

This sadly creates the very same error message. It might be aquivalent to what I tried before.

[v-jefferni]

Hi @ToubleShoot123 ,

It seems like caused by the connector, which exact data source are you using?

Best regards,

[ToubleShoot123]

Hi @v-jefferni 

Sorry for the late answer, I am on vacation for now but will also try to Check into this thread from time to time.

The Database should be a Microsoft SQL Server. I will be able to double Check at the start of January.

Thanks for the help so far and I wish you happy holidays!

[v-jefferni]

Hi @ToubleShoot123 ,

Using Patch to edit SQL server records requires the primary key value. So, please try the formula below:

ForAll(
        gal_Table_Columns.AllItems As GA;
        Patch(
            ReferencedObjectColumns;
            {FieldType: GA.drp_Table_FieldType.Selected.ID);
            {
                ...
            }
        )
)

Best regards,

[ToubleShoot123]

Happy New Year @v-jefferni ,

I don't think this is quite the solution.

It tosses the very same error. Also I don't really understand how you can pull out the Field to set from the remaining fields and put it where in the Patch you would usually require a specific Record/Entry.

Or is it that you are suggesting to Patch the entries where the primary ID matches that of the Fieldtype Column?

That however would be bad, since the FieldType Table only has 3 entries. I wll try to make it very clear what we are doing here, as I feel I have not really managed to convey what I want to achieve here.

This is the SQL table behind ReferencedObjectColumns:

ID	ColumnName	DataType	FieldType	...	...
1	"Savings"	"int"	1
2	"Costs"	"in"	2
3	"Description"	"nvarchar"	1

Here FieldType is just the primary ID of another table.

ID	Name
1	"Dimension"
2	"Fact"
3	"Key"

Having this additional table allows easily taking care of the values required for the dropdown. In SQL erms we would also join these two tables together whenever required.

When we use the ForAll Patch Command we want to patch the ReferencedObjectColumns Table.
For fields like "ColumnName" which are filled using just a textInput, this works even with the Initial method described in my very first post.

The only place this makes a problem ist when trying to Patch from a dropdown Box that got its values from the FieldTypes Table. This feels like it should not be so difficult and in fact just using Patch in the OnChange method of the Dropdown works perfectly fine, but this is proving a headache.

BTW just to confirm again. It's a Microsoft SQL Server. Just checked it.

While we aren't really moving forward, I still want to thank you for your help attempts. Maybe we will figure it out.