Using a repeating group for 2 data sets

[leyline]

Hi All, thanks in advance for your advice on this.

I am trying to use repeating groups to show a Tax Summary at the bottom of a quote.

Data is organised as follows:

1. Quote record - this holds the main quote information

2. Line items - this is an individual line item added to a quote, that when saved, is linked to the quote record and the quote record is linked to the line item (data linked in both directions).

3. Tax Rates - clients can set their own tax rates. When you create a line item, you must enter a tax rate. The Tax Rate is then selected in the Line Item. Tax rates are not linked to a Line Item as they are usable on mutiple line items (so the Tax rate is only saved in the Line Item - the Tax Rate does not have a list of all Line Items that are linked to that Tax Rate).

A quote can have multiple line items, and each line item can have a different tax rate. A line item has a cost excluding tax, and then calculates the cost including tax based. It also has a tax field showing the total amount of tax based on the Tax Rate selected.

I need a way to show the tax rates that are selected with the line items. Then next to it, so a summary of the tax amount of that tax rate (the total amount of tax for all line items with that tax rate). I don’t want to show Tax Rates that are not linked to a Line Item.

I started trying to use a repeating group, but having issues as I can’t link 2 data sets. I have managed to show all of the line items one by one, but can’t for the life of me work out how to remove duplicate Tax Rates, and have a sum of each line items tax amount next to it for that rate.

Here’s an image of my RG - there’s 3 line items. 2 have 20% VAT as a tax rate. 1 has No VAT (0%) as a tax rate. How would you go about having 1 line for 20% VAT in a RG and combining the £180 and £1920 together?

[mitchbaylis]

need to use group by

group by tax rate, sum of tax total

the group by reference can just be the line items repeating group

[leyline]

Thank you @mitchbaylis

For anyone requiring more detail, I used the Group by function in the repeating group data source:

With the grouping setup as follows:

And then in the repeating group content, used this:

and this:

h

This dispalyed the correct data in my repeating group.