Hi all,

I've got this table:

+------------+------------+----------+---------+
|   StartDate |   EndDate | VATCode | Rate   |
+------------+------------+----------+---------+
| 01/12/2008 | 31/12/2009 |    GB001 |   15 |
| 01/01/2010 | 31/12/2011 |    GB001 | 17.5 |
| 01/01/2012 | 31/12/9999 |    GB001 |   20 |
+-----------------------------------------------+

I'm querying a db for a particular invoice and given the date of that invoice I only want the lines which fit the date range from the above table.

At the moment I've got the following on a formula field:

IF
{SINVOICE.ACCDAT_0} in currentdate
THEN
ToText ({TABVAT.VATRAT_0}) + "%"
ELSE IF {SINVOICE.ACCDAT_0} in Date({TMPRATE.STRDAT_0}) to Date({TMPRATE.EndDate})
THEN
ToText ({TMPRATE.Rate}) + "%"

This however is bringing up three lines for each record on my invoice lines.

So ive got:

+---+----------+------+------+-------+
| ID | Product | NET | VAT | GROSS |
+---+----------+------+------+-------+
| 1 | water 1L | £1 | 20% | £1.20 |
| 1 | water 1L | £1 | NULL | NULL |
| 1 | water 1L | £1 | NULL | NULL |
| 2 |   pizza   | £10 | 20% | £12 |
| 2 |   pizza   | £1 | NULL | NULL |
| 2 |   pizza   | £1 | NULL | NULL |
+------------------------------------+

I managed to suppress the blank lines with

if len({@VAT Rate}) < 1 then true

on the selection expert suppress on the details line although that then stop the running total sum of the gross value for all the invoice lines.

Anyone has any idea how I can get only the populated lines to begin with so the sum process is less complicated?

Sorry I'm still a n00b on this stuff :))

Thanks!

Any help will be extremely appreciated <3


Edited by absiin - 10 Mar 2015 at 1:39am