cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Eduardo-Londono
Level: Power Up

Add values in a one to many connection

Hello everyone.

 

I have two tables, sales and items. the sales table has an Id (and some more values irrelevant right now) and the items table have it's own id, the id of the sale and a value.

 

I want to show the total value of the sale by adding the individual values of each items where the sale's id match, but I have no idea how to do this.

 

Thank you in advance for any help you can give me

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Add values in a one to many connection

Hi @Eduardo-Londono,

Could you please share more details about the data structure of your Sales table and Items Table?

Do you want to add extra column values from Items Table into your Sales table based on the corresponding Sale's Id Value?

Based on the needs that you provided, I think the AddColumns function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

 

AddColumns(
SalesTable,
"NewColumn1", /* <-- NewColumn represents the new added column name within your SalesTable*/
LookUp(ItemsTable, ItemId = SalesId, FieldName1),
"NewColumn2",
LookUp(ItemsTable, ItemId = SalesId, FieldName2),
...
)

Note: The FielsName1, FieldName2, .. etc represent the columns in your Items Table.

Please check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/How-to-join-2-data-sources/td-p/75631

More details about AddColumns function, please check the following article:

AddColumns function

 

If you want to sum/calculate the total value of the sale based on above result table, please take a try with the following formula:

Sum(
AddColumns(
SalesTable,
"NewColumn1", /* <-- NewColumn1 represents the new added column name within your SalesTable*/
LookUp(ItemsTable, ItemId = SalesId, FieldName1)
),
NewColumn1
)

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
TimRohr
Level 10

Re: Add values in a one to many connection

Look at Sum(); it has an implementation/overload where you are supplying a Table (ie, datasource), and a Numerical formula.

 

So you would need Sum(), Filter(), and possibly Collect() and AddColumn().

 

Sum(Filter(items,saleid=idFromSalesTable),ValueField)

In that, you'd have to replace your idFromSalesTable with a reference to the id field in your sales table... which might have different syntax depending on how you are connecting to it. And "saleid" is the name of the name of the column in the items table that represented the id of the sale (ie, the foreign key), and ValueField is the field you are summing.

 

Now, since this is a field that doesn't normally exist, you will need to Collect() your sale table, and at the same time use AddColumn() to add this field to it.

Super User
Super User

Re: Add values in a one to many connection

Hi @Eduardo-Londono,

There are several ways to show a One to many relationship in Powerapps.  I created an example using a simplified app with Customers and Orders.  The two tables are represented by the upper (Customers) and lower gallery (Orders).  The Orders gallery is sorted and filtered on the CustomerID as follows so that it shows the most recent order first:  

Sort(Filter(Orders,CustomerID=CustomersGallery.Selected.ID),OrderDate,Descending)

Each Gallery is associated with an Edit form.  The one for the Customer gallery is simple. The OnSelect formula for the  + sign in the customer header is 

NewForm(EditCustomerForm);Navigate(EditCustomerScreen, ScreenTransition.None)


CustomersandOrdrers.PNGCustomerDetails.PNG

and allows the user to add a new customer.  The > in the customer gallery navigates to the EditCustomerScreen and uses EditForm(EditCustomerForm); Navigate(EditCustomerScreen,None)  instead of NewForm as in the box above. That allows the user to edit and save the details of the selected customer. EditOrders.PNG

 The Orders gallery is set up in much the same way with it's own Edit form.  However, the new form does not allow the user to enter an order for a customer that does not exist. The CustomerID card is disabled to prevent the user from changing it. The DataCardKey has been changed to from Parent.DisplayName to "Customer" and the textinput control inside has been changed to CustomersGallery.Selected.CustName.  The Update property of the card has been changed to CustomersGallery.Selected.ID  .  The OrderID has also been disabled and the default value of the TextInput control inside has been changed to If(IsBlank(Parent.Default),First(Sort(Orders,ID,Descending)).ID+1,Parent.Default).  The OnSuccess property of the form has been changed from false to Back().  The reason for the First(Sort(Orders,ID,Descending)).ID+1 is to avoid delegation issues if the number of orders exceeds 2000.  

 

 

 

 

 

withTotal.PNG

 The total amount can be added outside of the gallery by inserting a Label with the Text property Sum(Filter(Orders,CustomerID = CustomersGallery.Selected.ID), OrderAmount). Note that the Sum() function takes the same filter condition as the Orders gallery Items property but adds the field name for the Value being summed.

 

By designing the app in this way, the One to many relationship is maintained and the user must add a new customer before adding the order. There are several variations on this design.  If there are many customers, a Dropdown control can take the place of the Customers gallery and use the Customers table as its Items property.  The Orders gallery would then be filtered on  Dropdown.Selected.ID.  If there were more than 500 customers, a Combobox control could be used instead since Dropdown controls are limited to 500 items.  

 

 

 

Community Support Team
Community Support Team

Re: Add values in a one to many connection

Hi @Eduardo-Londono,

Could you please share more details about the data structure of your Sales table and Items Table?

Do you want to add extra column values from Items Table into your Sales table based on the corresponding Sale's Id Value?

Based on the needs that you provided, I think the AddColumns function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:

 

AddColumns(
SalesTable,
"NewColumn1", /* <-- NewColumn represents the new added column name within your SalesTable*/
LookUp(ItemsTable, ItemId = SalesId, FieldName1),
"NewColumn2",
LookUp(ItemsTable, ItemId = SalesId, FieldName2),
...
)

Note: The FielsName1, FieldName2, .. etc represent the columns in your Items Table.

Please check and see if the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/How-to-join-2-data-sources/td-p/75631

More details about AddColumns function, please check the following article:

AddColumns function

 

If you want to sum/calculate the total value of the sale based on above result table, please take a try with the following formula:

Sum(
AddColumns(
SalesTable,
"NewColumn1", /* <-- NewColumn1 represents the new added column name within your SalesTable*/
LookUp(ItemsTable, ItemId = SalesId, FieldName1)
),
NewColumn1
)

 

Best regards,

Kris

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

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

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 181 members 4,819 guests
Please welcome our newest community members: