cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AVTS
Super User
Super User

Syntax for joining dataverse tables & Flow Collections - Canvas App

I am having a heck of a time trying to understand the syntax of powerapps.

 

I have a background in SQL and I am very comfortable with it. This is not SQL :(.

 

I have an Dataverse Table. My Dataverse was built correctly.And another side collections is created which comes from flow .

 

Here is what I have.

 

Dataverse table : Datasource: Dailyinventory

 

AVTS_0-1621877676854.png

 

Collections : obstock

 

AVTS_1-1621877798924.png

 

Expected Report Output :

 

I need to display the daily inventory data alongwith unstock from Collections

 

AVTS_2-1621878073626.png

 

exisiting formula used for dailyinventory data.


With( {TheTable:Filter(ShowColumns(Dailyinventory,"crf99_sno","crf99_materialdesc","crf99_materialno","createdon","crf99_bbd","crf99_qty"),DateDiff(createdon, Today(),Days) =0)},
ShowColumns(AddColumns(GroupBy(TheTable,"crf99_materialdesc","crf99_materialno","NewGroup"),

"Created On",Last(NewGroup).createdon,
"bbd",Last(NewGroup).crf99_bbd,
"Qty",Sum(NewGroup,crf99_qty)),"Created On","bbd","crf99_materialdesc","crf99_materialno","Qty"))

 

Need direction and hint to join the both with collections and tables.

 

Thank you in advance to anyone willing to help me out with this.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@AVTS 

Please consider changing your Formula to the following:

With({TheTable: Filter(Dailyinventory,
                    DateDiff(createdon, Today(),Days) =0
                )},
    AddColumns(
        AddColumns(
            GroupBy(TheTable,
               "crf99_materialdesc", "crf99_materialno", "NewGroup"
            ),
            "Created On", Last(NewGroup).createdon,
            "bbd", Last(NewGroup).crf99_bbd,
            "Qty", Sum(NewGroup, crf99_qty)
            "Unstock", LookUp(obstock, Materialdesc=crf99_materialdesc && Material=crf99_materialno, Unstock)
        ),
        "Diff", Unstock - Qty
    )
)

 

This should provide the output you are looking for based on the information you provided.

 

Also, in this scenario, the ShowColumns function is only going to slow down your performance.  I have removed them from the formula as there is no value derived. 

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
AVTS
Super User
Super User

Any resolver available online. @RandyHayes  can you share your suggestion . . 

RandyHayes
Super User
Super User

@AVTS 

Please consider changing your Formula to the following:

With({TheTable: Filter(Dailyinventory,
                    DateDiff(createdon, Today(),Days) =0
                )},
    AddColumns(
        AddColumns(
            GroupBy(TheTable,
               "crf99_materialdesc", "crf99_materialno", "NewGroup"
            ),
            "Created On", Last(NewGroup).createdon,
            "bbd", Last(NewGroup).crf99_bbd,
            "Qty", Sum(NewGroup, crf99_qty)
            "Unstock", LookUp(obstock, Materialdesc=crf99_materialdesc && Material=crf99_materialno, Unstock)
        ),
        "Diff", Unstock - Qty
    )
)

 

This should provide the output you are looking for based on the information you provided.

 

Also, in this scenario, the ShowColumns function is only going to slow down your performance.  I have removed them from the formula as there is no value derived. 

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
AVTS
Super User
Super User

Thank you RandyHayes

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,139)