cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bscramling
Level: Powered On

ForAll, Patch, Filter with SharePoint, Collection and SQL Table

Hello all,

 

I have the following...

 

Collection "PartQtyChanged"

  • Columns = 'PartNo' and 'QtyToRemove'

 

SQL Table 'InvUnControlled'

  • Columns = 'ID' (Primary Key, Identity), 'PartNo' (nchar(15)), 'PartDescription' (nchar(30)) and 'QtyOnHand' (smallint)

 

SharePoint List 'PartList'

  • Columns = 'PartNumber', 'PartDescription', 'PartImage', 'PartCategory' and other misc columns...

 

Gallery 'glr_PartsList'

  • Values taken from SharePoint List 'PartList', specifically 'PartNumber', 'PartDescription' and 'PartImage'
  • Contains a slider 'sld_QtyRemoved' that the user sets a number between 0-50 for parts removed from stock
    • Slider has OnChange event...
    • RemoveIf(PartQtyChanged,ThisItem.PartNumber = PartNo);Collect( PartQtyChanged, { PartNo: ThisItem.PartNumber, QtyToRemove: sld_QtyRemoved.Value})

Everything is working great up to this point.

 

However, after the user changes various sliders for the parts removed they are required to click a Submit button to record the amounts in the SQL Table 'InvUnControlled' using the following code...

 

ForAll(
    PartQtyChanged, //Collection
    Patch(
        '[dbo].[InvUnControlled]', //SQL Table
        Filter(
            PartQtyChanged,
            QtyOnHand > 0       //Only those Parts whose sliders do not equal 0
        ),
        {
            PartNo: lbl_PartNumber.Text,
            QtyOnHand: QtyOnHand - sld_QtyRemoved.Value,
            PartDescription: lbl_PartDescription.Text
        }
    )
)

...which is obviously incorrect. I think I have the syntax/order of execution all wrong for "ForAll", "Patch" and "Filter".

 

Can someone please point me in the right direction?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
TimRohr
Level 10

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

Well, since you're willing to modify your Collection (or if you are willing to create a new one), then create one that is an exact match of the SQL table, but which has the QtyOnHand already reduced by the amount of your slider. Then you can bypass the ForAll() completely because Patch() can bulk update provided the source and destination datasources have the same columns...

 

So if you re-use the PartsQtyChanged Collection to be the copy of the SQL table, then you should be able to just say:

Patch('[dbo].[InvUnControlled]', PartsQtyChanged)

 

It doesn't get much simpler than that.

30 REPLIES 30
TimRohr
Level 10

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

Looking at it quickly, your Patch() statement is going to expect a record for the 2nd argument, but you're feeding it a table (the result of a Filter()). In fact, I'm not sure what you're doing with the Filter(), because you're working with the same Collection as the initial ForAll() statement... so why wouldn't you filter it at the ForAll() level?

 

As for what record you choose for the Patch() statement, you can do something elegant in choosing the record that you are going to patch... where your Collection and the datasource of the Patch() are similar enough that if you select a record out of your Collection but try to Patch() to your datasource, the correct record will be located and updated in your datasource.

 

But if that gets at all confusing, you can simply use a First(Filter()) of your datasource in the Patch() statement, providing a logical test in the Filter() statement that matches a record from the datasource to the record in question (during the ForAll() walk-through of the Collection).

 

Something like...

ForAll(
    Filter(PartQtyChanged, QtyOnHand > 0), //Collection
    Patch(
        '[dbo].[InvUnControlled]', //SQL Table
        First(
Filter( '[dbo].[InvUnControlled]', yourIDField = PartQtyChanged[@yourIDField] //Only those Parts whose sliders do not equal 0
) ), { PartNo: lbl_PartNumber.Text, QtyOnHand: QtyOnHand - sld_QtyRemoved.Value, PartDescription: lbl_PartDescription.Text } ) )

As for the order, that looks correct... See here for more info:

https://powerapps.microsoft.com/en-us/blog/bulk-update-using-forall-and-patch/

 

Post back if you need more help.

bscramling
Level: Powered On

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

@TimRohr,

 

Thanks for your reply.

 

I tried your code but I am getting an "Invalid Agrument Type" error at the "=" with this...

 

First(
            Filter(
                '[dbo].[InvUnControlled]',
                PartNo = PartQtyChanged[@PartNo]
            )
        ),

I assume the first 'PartNo' is looking at [dbo].[InvUnControlled] and the second 'PartNo' is referring to the filtered PartQtyChanged collection. Or am I completely clueless here?

 

EDIT: The PartNo to the left of the "=" sign is type 'varchar(50)' in my SQL table.

TimRohr
Level 10

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

Yes, the first would refer to the source table for the Filter(), and the second would refer to the Collection.

 

So... do you Collect() records into the Collection straight from the SQL table? That is, are the fields supposed to be the same type?

 

Let me see if I can replicate your setup. I aircoded that previous reply with a short amount of time. I might have missed something.

bscramling
Level: Powered On

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

@TimRohr,

 

The Collection values are created from a Gallery which is populated from a SharePoint List. Every time a user changes the slider value (QtyRemoved) the following OnChange event is executed...

 

RemoveIf(PartQtyChanged,ThisItem.PartNumber = PartNo);Collect( PartQtyChanged, { PartNo: ThisItem.PartNumber, QtyRemoved: sld_QtyRemoved.Value})
TimRohr
Level 10

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

So you are trying to match a field from a SP List to your SQL table. In SQl it is a varchar(50)... and I'm guessing in SP it is a Single Line Text?

bscramling
Level: Powered On

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

Yes, exactly.

TimRohr
Level 10

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

OK... got it sorted. The disambiguation operator (@) can't be used on a table arising from an expression (like Filter()). So we need to get creative. Literally. We're going to create a new column with duplicate info so we don't have to use the disambiguation operator. This can be accomplished by adding the column to your Collection when you create it (and then filling it with duplicate ID info every time you do a Collect() to it), or you can do it at the end using AddColumn() in the ForAll().

The caveat here is that since you can't use it with a Filter(), you have to either (a) be OK with an If() statement to check whether the record should be processed (which can lead to delays if you are checking a large number of records), or (b) Re-Collect your filtered Collection prior to your ForAll(), and then use that as the source for the changes you want to apply. I will present the solution both ways. You can use the one that makes the most sense.

(Note: this is writing your field names over the formulas that I arrived at using my data, and could have syntactical problems in your environment if I mistype something.)

Option A: Use If() statement

ForAll(
    AddColumns(PartQtyChanged,"IDPartNo",PartQtyChanged[@PartNo]),
If ( QtyOnHand > 0, Patch( '[dbo].[InvUnControlled]', First(

Filter( '[dbo].[InvUnControlled]', PartNo = IDPartNo
) // END FILTER ), // END FIRST { PartNo: lbl_PartNumber.Text, QtyOnHand: QtyOnHand - sld_QtyRemoved.Value, PartDescription: lbl_PartDescription.Text } ) // END PATCH
) // END IF ) // END FORALL

Option B: Filtering into a new Collection first

Collect(PartQtyChanged_NonZero, Filter(PartQtyChanged,QtyOnHand > 0);
ForAll( AddColumns(PartQtyChanged_NonZero,"IDPartNo",PartQtyChanged_NonZero[@PartNo]),
Patch( '[dbo].[InvUnControlled]', First(

Filter( '[dbo].[InvUnControlled]', PartNo = IDPartNo
) // END FILTER ), // END FIRST { PartNo: lbl_PartNumber.Text, QtyOnHand: QtyOnHand - sld_QtyRemoved.Value, PartDescription: lbl_PartDescription.Text } ) // END PATCH
) // END FORALL

Also, one last thing... Why are you updating the PartNo if that is the unique field that is driving the connection between your SP list and the SQL table? If that is changing as a part of your app's processes, then you won't arrive at the proper record in SQL anyway. That seems like a superfluous field to be writing... it should never be changing... at least as I understand your setup.

bscramling
Level: Powered On

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

@TimRohr,

 

First of all, thank you for the time and effort you are putting into this. I can't thank you enough. I am going to try each method and see how they work. I will get back to you on the results.

 

Finally, to be honest, I wasn't even paying much attention to the updating fields since I wasn't able to get the first part working. You are absolutely correct that the PartNo (nor the PartDescription) needs updating. I just have to figure out how to deduct the value of QtyRemoved (Collection) from the existing QtyOnHand (SQL) value.

bscramling
Level: Powered On

Re: ForAll, Patch, Filter with SharePoint, Collection and SQL Table

@TimRohr,

 

This code...

 

ForAll(
    AddColumns(
        PartQtyChanged,
        "IDPartNo",
        PartQtyChanged[@PartNo]
    ),
    If (
        QtyRemoved > 0,
        Patch(
            '[dbo].[InvUnControlled]',
            First(
                Filter(
                    '[dbo].[InvUnControlled]',
                    PartNo = IDPartNo
                )// END FILTER
            ),// END FIRST
            {QtyOnHand: QtyRemoved}  // Need to subtract QtyRemoved from existing QtyOnHand value and replace QtyOnHand with new value
        )// END PATCH
    )// END IF
)// END FORALL

worked perfectly. This will never be a large table (< 100 records) so there shouldn't be a problem.

 

I also changed this code from the slider (QtyRemoved) OnChange event...

 

RemoveIf(PartQtyChanged,ThisItem.PartNumber = PartNo);Collect( PartQtyChanged, { PartNo: ThisItem.PartNumber, QtyRemoved: sld_QtyRemoved.Value})

...to this...

RemoveIf(
    PartQtyChanged,
    ThisItem.PartNumber = PartNo
);
If(
    sld_QtyRemoved.Value > 0,
    Collect(
        PartQtyChanged,
        {
            PartNo: ThisItem.PartNumber,
            QtyRemoved: sld_QtyRemoved.Value
        }
    )
)

...so now the Collection PartQtyChanged will only hold records of actuals parts whose value has changed. So I guess I can remove the If statement before the Patch.

 

One last issue, and I racked my head about this but can't figure out how to do it, but can you offer any advice about the statement in red in the top code snippet. I can't figure out how to grab the QtyOnHand column value and subtract the QtyRemoved value before replacing the result back into the QtyOnHand column. Every way I have tried gives me an error.