How to populate all items in a dataset with aggregated (Sum) data from another dataset?

Ask the community
, ,
1

I have a Vendors Dataset. My vendors earn money and those transactions are stored in a Transactions Dataset.

I would like to add a Sum of Payments column in my Vendors Dataset. Meaning I should probably aggregate the Transactions by Vendor ID.

How can I do this in a simple code.

I don’t even want to put code here as I don’t know what to start with. Is it a Transactions Aggregate grouped by “vendor ID” or do I start with a Vendors Query and inside that I have a Transactions Aggregate?

I’m completely blocked as to how to logically code this. I have basic code experience, and I think this should be relatively simple, but never done it before.

What is the logical order of events? And maybe an example of code would be really appreciated. Thanks.

2

Hello! I have a couple of questions and then am happy to see if I can help you problem solve.

When do you want the aggregate column to update? For example, will this be a job that runs nightly to update the data or will the numbers update when a vendor logs into their dashboard to look? Or on some other site actions?

3

I would probably create an earnings tab in the vendors dataset then every time a purchase is made query the vendors dataset and set the filter to the vendor ID. You can return the earnings table. From there just add the total amount of the new purchase to the earnings amount and save the new updated amount in the vendors database. The query would look something like this:

wixData.query("Vendors").hasAll("vendorID", Value)
.then(vendorInfo => {
	let transactionTotal = vendorInfo.earnings + price
	$w("#Vendors").setFieldValue("earnings", transactionTotal)
	$w("#Vendors").save()
}

This code SHOULD work to get you started. I would test this on a dummy database just in case. The only thing that I am not 100% sure on is calling setFieldValue inside the .then statement. In theory it should work but it might be smart to test it out before changing any data in the live dataset.

4

Hi Amanda, thanks for your response.

Essentially there are 2 pages:

  1. Vendors Balance (All)
  2. Vendors Balance (Dynamic Page)

The 2nd one works no problem. I pull that data live and filter by the Dynamic Page.

The 1st Vendors Balance (All) is the page I’m having difficulty with. The reason is because I am wanting to update the entire database list and display all of them on a Repeater.

So the data I want to display is:
DB Vendors - Name
DB Vendors - ID
DB Transactions - Total Payments (Sum) per Vendor (this is currently just individual transaction line items on Transactions DB)

The option is to either update an aggregated field on DB Vendors Item and then query THAT number for the repeater, or
Aggregate the Transactions DB and filter by Vendors somehow.

I simply want to open the Vendors Balance (All) list, press a button and then the data updates and displays on the repeater.
Name, ID and Sum of Payments

5

Thank you for your response.

I wrote a response above. The example you wrote for me already works fine. I can make the dynamic page display data fine.

It’s on the Vendors All page where I am struggling to pull data, update the data and then populate the entire repeater for all vendors.

6

@kscholiadis You have to create a link between the Vendors and Transactions databases. I thought this was already done. If not here is a link on how to do that. About Connecting Multiple Database Collections | Help Center | Wix.com
You basically just need to create a reference field on the transactions database that points to the primary field of the Vendor’s database. Then your repeater will have access to the transactions database. Wix uses a SQL like relational database structure under the hood to manage datasets on their sites. So if you are familiar with creating foreign and primary keys this functions exactly the same.

If you are dealing with live data and need a way to refresh the database in real time you can just chain a .refresh() when updating the transaction total refresh - Velo API Reference - Wix.com