cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
echodapogi
Resolver I
Resolver I

Combining two tables with variance

Hi there, 

 

I am reaching out again in this forum if anybody can figure out how to combine this two tables. I'm out of ideas in my mind. 

I need a summary of what has been requested vs what has been received. I need to display it using data table in powerapps if possible. Please Help. 

 

Thanks in advance.

 

 

MainrequestTable 

LocationNumberLocationNameModelQty
1111Austin LocationIphoneX3
1111Austin LocationSamsung4
2222Michican LocationNokia5

 

ReceivedTable

recLocationNumberrecLocationrecModelreceivedqty
1111Austin LocationIphoneX2
1111Austin LocationSamsung2
2222Michican LocationNokia3

 

is there a way to combine these two tables? like joining them. using data table in powerapps?

like the below summary?

Location NumberLocation NameModelRequested QtyReceived QtyVariance
1111Austin LocationIphoneX321
1111Austin LocationSamsung422
2222Michican LocationNokia532

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
WarrenBelz
Super User III
Super User III

HI @echodapogi ,'

This will only work properly if your Received Table items numbers in under your Delegation limit as relational lookups are not Delegable, but try this

With(
   {wReceived:ReceivedTable},
   RenameColumns(
      AddColumns(
         MainRequestTable,
         "ReceivedQty",
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty,
         "Variance",
         Qty - 
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty
      ),
	  "qty",
	  "Requested Qty"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

@echodapogi ,

Just turn the last one around for the negative and filter as below

With(
   {wReceived:tblReceivedSerials},
   SortByColumns(
      RenameColumns(
         AddColumns(
            Filter(   
               xlsPullbackTablePlanningList,
               IsBlank(YourSearchBox.Text) || 'Location Number = YourSearchBox.Text
            ),
            "Qty",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty,
            "Variance",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty - 'Qty to pullback'      
         ),
         "Qty",
         "Received Qty"
      ), 
     'Part Location Number' = txtLocationNo.Text && Variance <> 0
   ), 
   "Variance", 
   Descending)
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

11 REPLIES 11
WarrenBelz
Super User III
Super User III

HI @echodapogi ,'

This will only work properly if your Received Table items numbers in under your Delegation limit as relational lookups are not Delegable, but try this

With(
   {wReceived:ReceivedTable},
   RenameColumns(
      AddColumns(
         MainRequestTable,
         "ReceivedQty",
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty,
         "Variance",
         Qty - 
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty
      ),
	  "qty",
	  "Requested Qty"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

View solution in original post

hi warrenbelz, thank you for you reply. where i need to put this code? is the datatable items property?

Hi Warren, i figured it out how to make it work from your code. one more question is there a way to add filter on this? like filter by Location Number?

just want to say THANK YOU WARREN. it helps a lot. 

Hi @echodapogi ,

As an example if you had a Text Box called txtLocationNo, you would do this

With(
   {wReceived:ReceivedTable},
   RenameColumns(
      AddColumns(
         Filter(
            MainRequestTable,
            IsBlank(txtLocationNo) || StartsWith('Location Number',txtLocationNo.Text)
         ),
         "ReceivedQty",
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty,
         "Variance",
         Qty - 
         Lookup(
            wReceived,
            recLocationNumber = LocationNumber
         ).receivedqty
      ),
	  "qty",
	  "Requested Qty"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi Warren, 

I was able to apply it to my actual case but i have this very little issue on this particular view.

I need the Positive value to be Negative (-) and i need the negative value to be positive in the Variance column

I tried to recode it but no luck. can you please please help?

 

Here is the code. 

With(
   {wReceived:tblReceivedSerials},
   SortByColumns(Filter(RenameColumns(
      AddColumns(
         xlsPullbackTablePlanningList,
         "Qty",
         LookUp(
            wReceived,
            SKUReceived  = SKU_x0020__x0023_
         ).Qty,
         "Variance",
         'Qty to pullback' - 
         LookUp(
            wReceived,
            SKUReceived  = SKU_x0020__x0023_
         ).Qty
      ),
	  "Qty",
	  "Received Qty"
   ), 'Part Location Number' = txtLocationNo.Text && Variance <> 0), "Variance", Descending)
)

 

Qty_x0020_to_x0020pullback is the requested quantity

Received Qty is the actual received items

 

I need the two values of (-2) to (2) and (-5) to (5) (positive)

and all other values is negative. How can i do that. please help.

echodapogi_1-1614271060405.png

 

@echodapogi ,

Just turn the last one around for the negative and filter as below

With(
   {wReceived:tblReceivedSerials},
   SortByColumns(
      RenameColumns(
         AddColumns(
            Filter(   
               xlsPullbackTablePlanningList,
               IsBlank(YourSearchBox.Text) || 'Location Number = YourSearchBox.Text
            ),
            "Qty",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty,
            "Variance",
            LookUp(
               wReceived,
               SKUReceived  = SKU_x0020__x0023_
            ).Qty - 'Qty to pullback'      
         ),
         "Qty",
         "Received Qty"
      ), 
     'Part Location Number' = txtLocationNo.Text && Variance <> 0
   ), 
   "Variance", 
   Descending)
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Thanks WarremBelz. You're the BEST. 

Hi Warren, 

 

Hello again, Please help. How can i invert the positive to negative and negative to positive on this code? 

I manipulated the code you gave me but im struggling to invert the positive and negative. 

 

 

          
           
    With(
   {wReceived:xlsPullbackTablePlanningList},
   SortByColumns(Filter(RenameColumns(
      AddColumns(
         tblReceivedSerials,
         "Requested",
         LookUp(
            wReceived,
            'SKU #'  = SKUReceived && PartLoc = 'Part Location Number'
         ).'Qty to pullback',
         "Variance",
          
         LookUp(
            wReceived,
            'SKU #'  = SKUReceived && PartLoc = 'Part Location Number'
         ).'Qty to pullback' - Qty
      ),
	  "Qty",
	  "Received Qty"
   ), PartLoc =TextInput6_2.Text && Variance <> 0), "Variance", Descending)
)

 

 

The 2 should be -2 and the 1 should be -1, all other should be positive. all fours are positive

Please help. 

echodapogi_0-1614651804015.png

 

Thank you again. 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (83,057)