cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimRohr
Level 10

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

I'm guessing you don't know the starting quantity from the SP list? You don't know it until you access the SQL table?

bscramling
Level: Powered On

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

Unfortuantely that is correct. I was hoping there would be a way to extract it during the ForAll statement but I'm shooting blanks.

TimRohr
Level 10

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

Wellllllllllllllllll...

 

...you could use an AddColumns() statement to reCollect your Collection prior to the ForAll(), but this time adding in the Quantity field from the SQL table. You might even be able to handle the math in that one step. If not, the field will be available in your Collection for use during the ForAll().

TimRohr
Level 10

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

Something like...

Collect(
    PartQtyChanged_SQLQty,
    AddColumns(
        PartQtyChanged,
        "WriteQty",
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo=PartQtyChanged[@PartNo]
        ).QtyOnHand - QtyRemoved
    )
)

And, since you're already doing that much, you might as well add your "IDProdNo" column at the same time, and save yourself the step in the ForAll().

bscramling
Level: Powered On

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

@TimRohr,

 

Unfortunately, I'm back to being confused. I added your code...

Collect(
    PartQtyChanged_SQLQty,
    AddColumns(
        PartQtyChanged,
        "NewQty", //Changed the name of the column.
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo=PartQtyChanged[@PartNo]
        ).QtyOnHand - QtyRemoved
    )
);

...but I am at a lost as to what I need to change in the ForAll statement.

 

Based on what you said, should I remove the AddColumns in the ForAll()? But if I do that I don't know how to change the "PartNo = IDPartNo" in the Filter(). I thought maybe something like this...

PartNo = PartQtyChanged_SQLQty[@PartNo]

...but that errored out like crazy. I tried changing this...

{QtyOnHand: QtyRemoved}

...to this...

{QtyOnHand: PartQtyChanged_SQLQty[@NewQty]}

...but that got errors as well.

 

Sorry to be such a pain, and as I said, I do appreciate your help.

TimRohr
Level 10

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

ok, first thing I would do is I would put the new Collect() statement in somewhere before the ForAll(). Trigger it to run, and then check your View-->Collections page from the PowerApps menu to see if you got the data you were expecting in the new column. (Basically,  you're checking my code against your environment to make sure I wrote it correctly.) If so, then you're good to move forward.

 

The smallest change to your code would be to replace the Table reference in the ForAll( AddColumns()) statement to refer to the new Collection:

ForAll(
    AddColumns(
        PartQtyChanged_SQLQty,
        "IDPartNo",
        PartQtyChanged_SQLQty[@PartNo]
    ),

That should have you reading from a Table...

...which represents your new IDPartNo field and the PartQtyChanged_SQLQty Collection...

...which represents your new NewQty field and the PartQtyChanged Collection.

 

Then your Patch() statement should use that directly:

{QtyOnHand: NewQty}

That much should work cleanly. If you want to tighten the code, though, you can keep going.

 

The test, here, will be whether the disambiguation operator can work in the more complex statement we are about to write. If so, great... you'll be doing this in one step instead of 2:

AddColumns() can add multiple columns at once, so if the Collect() statement is working, you can probably just combine the second AddColumn() statement with the first:

Collect(
    PartQtyChanged_SQLQty,
    AddColumns(
        PartQtyChanged,
        "NewQty",
        LookUp(
            '[dbo].[InvUnControlled]',
            PartNo=PartQtyChanged[@PartNo]
        ).QtyOnHand - QtyRemoved,
        "IDPartNo",                   // this line and the next came from the other AddColumns() statement
        PartQtyChanged[@PartNo]       // except this line now points to the orig Collection, since we're doing 1 step
    )
);

If that works (check the View-->Collections page again), then replace the ForAll( AddColumns()) with ForAll( PartyQtyChanged_SQLQty, Patch(...)). Since your Collection already has the fields you need, you don't have to operate on a table produced by an AddColumn() statement. Last, make sure that the Patch uses the "NewQty" field, as I showed in the code, just above.

 

bscramling
Level: Powered On

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

Unfortunately, the 'NewQty' field isn't showing the proper values. For testing purposes, I put the 'QtyOnHand' values in the SQL table all to "100". I set the various QtyRemoved values via the sliders to the following and got the numbers next to them in the NewQty column.

 

QtyRemoved    NewQty
50            -0
45            -45
40            -40
35            -35
30            -30
TimRohr
Level 10

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

what data type are the fields:

QtyOnHand (from SQL table)

QtyRemoved (from your Collection/Slider)

(You might need to wrap both in separate Value() functions to get the math to process correctly.)

Also, post your ForAll() statement so we can see what you're working with and where things stand now.

bscramling
Level: Powered On

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

QtyOnHand = smallint

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

 

Current ForAll() Statement:

 

ForAll(
    PartQtyChanged_SQLQty,
    Patch(
        '[dbo].[InvUnControlled]',
        First(
            Filter(
                '[dbo].[InvUnControlled]',
                PartNo = IDPartNo
            )// END FILTER
        ),// END FIRST
        {QtyOnHand: NewQty}
    )// END PATCH
)
bscramling
Level: Powered On

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

FYI...

 

I added Value() to QtyRemoved and the first record worked properly. The remaining records still turned out like I previously showed.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

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!

Users Online
Currently online: 165 members 5,372 guests
Please welcome our newest community members: