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

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

@TimRohr,

 

Here is where I am stuck...

 

This code...

 

ClearCollect(
    PartQtyChanged_SQLQty,
    AddColumns(
        PartQtyChanged,
        "NewQty",
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo = PartQtyChanged[@PartNo]
        ).QtyOnHand - Value(QtyRemoved),
        "IDPartNo",
        PartQtyChanged[@PartNo]
    )
)

...produces this...

 

Collection-PartQtyChanged.pngCollection-PartQtyChanged_SQLQty.png

 

The code only works if I change the "QtyRemoved" value for the first part listed in the gallery, which is 4535-673-90921. And it will only work for that particular record. If I don't change the "QtyRemoved" value for that record, then all the values end up like this...

 

Collection-PartQtyChanged-2.pngCollection-PartQtyChanged_SQLQty-2.png

 

Here is the InvUnControlled table...

 

dbo-InvUnControlled.png

Any ideas?

TimRohr
Level 10

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

Maybe try...

ClearCollect(
    PartQtyChanged_SQLQty,
    AddColumns(
        PartQtyChanged,
        "NewQty",
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo = PartQtyChanged[@PartNo]
        ).QtyOnHand - Value(PartQtyChanged[@QtyRemoved]),
        "IDPartNo",
        PartQtyChanged[@PartNo]
    )
)
bscramling
Level: Powered On

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

Already tried it. Same result.

TimRohr
Level 10

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

OK... new idea...

Replace the LookUp().QtyOnHand with a First(Filter()).QtyOnHand to see if that works better:

ClearCollect(
PartQtyChanged_SQLQty,
AddColumns(
PartQtyChanged,
"NewQty",
First(
Filter( '[dbo].[InvUnControlled]', PartNo=PartQtyChanged[@PartNo] )
).QtyOnHand - Value(PartQtyChanged[@Removed]),
"IDPartNo",
PartQtyChanged[@PartNo]
)
)

 If that doesn't work, I would troubleshoot by separating the math from the AddColumns() statement. When you first ClearCollect() the data, do an AddColumns() to get the new column there, but fill it with a simple, recognizable value (like 1000) for all records. Then do a ForAll( Patch()) to do the math on a record-by-record basis. This would be a new ForAll(), separate from the one that we are working on. See if you can get the formula right when you are just working on one datasource... it might be simpler.

bscramling
Level: Powered On

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

Thanks!

 

I will give it a shot. Everything I have tried so far either gives the same results or much worse. I will let you know the results.

bscramling
Level: Powered On

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

Well, I decided to take a slightly different approach. It's working a little better but I still have problems.

 

I've added the following code to the OnChange event for the slider (sld_QtyRemoved) in each gallery item..

 

RemoveIf(
    PartQtyChanged,
    PartNo = ThisItem.PartNumber
);
If(
    sld_QtyRemoved.Value > 0,
    Collect(
        PartQtyChanged,
        {
            PartNo: lbl_PartNumber.Text,
            QtyRemoved: sld_QtyRemoved.Value,
            QOH:LookUp(
                '[dbo].[InvUnControlled]',
                PartNo = lbl_PartNumber.Text
            ).QtyOnHand,
            NewQty:LookUp(
                '[dbo].[InvUnControlled]',
                PartNo = lbl_PartNumber.Text
            ).QtyOnHand - sld_QtyRemoved.Value 
        }
    )
)

I also did away with the Collection "PartQtyChanged_SQLQty" and now just use "PartQtyChanged". I added a 'QOH' column which retrieves the current QtyOnHand value from the SQL datasource. The NewQty value in the collection is the result of QtyOnHand (SQL datasource) - sld_QtyRemoved.Value (gallery slider control).

 

This code seems to be working fine. It updates the "PartQtyChanged" quickly and accurately.

 

However, when I try to submit the changes to the SQL datasource using any one of the three snippets below I am running into all kinds of problems...

 

ForAll(
    Filter(
        glr_PartsList.AllItems.sld_QtyRemoved,
        sld_QtyRemoved.Value > 0
    ),
    Patch(
        '[dbo].[InvUnControlled]',
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo = lbl_PartNumber.Text
        ),
        {QtyOnHand: Value(lbl_QtyOnHand.Text)-sld_QtyRemoved.Value}
    )
)
//-----------------------------------------------------------------------------------------------------------
ForAll(
    PartQtyChanged,
    Patch(
        '[dbo].[InvUnControlled]',
        First(
            Filter(
                '[dbo].[InvUnControlled]',
                PartNo = PartQtyChanged[@PartNo]
            )
        ),
        {QtyOnHand: Value(QOH) - Value(QtyRemoved)}
    )
)
//-----------------------------------------------------------------------------------------------------------
ForAll(
    PartQtyChanged,
    Patch(
        '[dbo].[InvUnControlled]',
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo = PartQtyChanged[@PartNo]
        ),
        {QtyOnHand: Value(QOH) - Value(QtyRemoved)}
    )
)

The problems are pretty much identical.

 

  1. Sometimes the code will execute and sometimes it won't.
  2. Sometimes if there are multiple changes it will only change one of them.
  3. Sometimes it will pick a random PartNo from the SQL datasource and update its QtyOnHand value with a value from another item.
  4. If I only change the QtyRemoved slider for the first item in the Gallery it will always work.

Obviously it is not scrolling/looping through the collection or gallery as I thought it would.

 

I even added a "Start Over" button with the following code but it doesn't help...

Clear(PartQtyChanged);
UpdateContext({SliderReset: true}); //Resets the slider to 0
UpdateContext({SliderReset: false});
Refresh('[dbo].[InvUnControlled]')

I've pretty much run out of ideas and any changes I make now are just wild stabs in the dark.

 

Any suggestions?

Highlighted
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.

bscramling
Level: Powered On

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

And that will only modify the records in the SQL datasource that are included in the collection? All other records will be untouched?

TimRohr
Level 10

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

yes, as long as you only Collect() the records that have modified values.

bscramling
Level: Powered On

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

I tried it and it was creating the correct records and values. Unfortunately, it was not updating the existing records but adding new ones and leaving the original records in place.

 

Then I remembered I had a ID field in the SQL datasource. I deleted that column and made the PartNo column KEY and it worked perfectly!!! (And extremely fast)

 

I can't epxress my appreciation enough for all your help in a situation that I unwittingly made much more complex than it needed to be.

 

Thanks again! Smiley Very Happy

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 26 members 3,854 guests
Recent signins:
Please welcome our newest community members: