Portal displaying a collection of invoices (all line items, not just totals)

[db_tragic]

Not sure if a portal is the best way for this but the scenario is as follows:

 

Have a database with the following key tables: ClientDetails(1 to many) -  Horses(1 to many) - Treatment Visit - Invoice

 

I have basic invoicing set up, associated with a given treatment visit (which is linked to the horse, and owner). The invoice layout is based on the concept of using a portal showing a separate line items table (as some invoices will have more than one service item added).

 

Here's what I want to do;

 

Have a button on the Client Details layout (labelled something like 'Aggregate invoice'), which goes to a layout that shows all invoices against that owner(don't worry about filtering for the moment). Now I did set up such a layout (based on the Client Details table), with a portal, containing the following fields: Date, Horse Name, Invoice Item, Amount,

 

This setup only shows the first line item appearing on any invoice - fine for single item invoices of course, but this won't always be the case.

 

Any ideas on why the portal won't display more than the first line item appearing on the invoices associated with that owner?

 

TIA

[johnlewisdesign]

I'd make a relationship purely for the purpose of serving that portal. If you have, check the fields on the portal actually read from the same Table Occurrence as the portal itself. The problem is the relationship being slightly wrong.

[db_tragic]

Thanks for the reply,

 

Was thinking about this and am not sure how I can fine tune the relationship to correct it. I have set the layout that displays the portal as being based on the 'Client Details' table - Have tried other tables in the relationship, but they do not populate the portal correctly.

 

Below is a copy of my table relationships of that helps (...you to help me  )

 

So to help clarify what I have been doing, is to have a portal showing the contents of another portal(date, line item, extended price) across a group of related records(Animals).

 

I'd make a relationship purely for the purpose of serving that portal. If you have, check the fields on the portal actually read from the same Table Occurrence as the portal itself. The problem is the relationship being slightly wrong.

[Brooks]

There are a couple issues try this. If you can't get it you can attach your file and one of us will usually modify it for you to show you how.

1. You need a summary field that totals the ext price in the line items table

2. Place a total field as an unstored calc field in the invoice table referencing the sum field in the line items table

3. Base your portal on the invoice table and show the unstored total calc

As a side note you will find it beneficial to add additional Fk_id for the other tables to the invoices table. This will allow you to build more flexible layouts and relationships as you move forward by not releying on the whole chain of tables. So if you stored the client Id in the invoices table you would then have a relationship to the invoices table directly from the client table for reporting purposes.

[db_tragic]

Hi Brooks,

 

Sorry I missed this post of yours - thank you for the advice. I'm just about to follow it through now and see how it goes.

 

Despite this, I'm still not entirely sure why any form of report that aggregates all invoices under a Client, only show the first line item on the invoice - are you suggesting that by including the extra relationships, it should resolve this?

 

Also, is  it best to create the extra connections by using separate TO's in the relationships graph?

 

 

There are a couple issues try this. If you can't get it you can attach your file and one of us will usually modify it for you to show you how.
1. You need a summary field that totals the ext price in the line items table
2. Place a total field as an unstored calc field in the invoice table referencing the sum field in the line items table
3. Base your portal on the invoice table and show the unstored total calc

As a side note you will find it beneficial to add additional Fk_id for the other tables to the invoices table. This will allow you to build more flexible layouts and relationships as you move forward by not releying on the whole chain of tables. So if you stored the client Id in the invoices table you would then have a relationship to the invoices table directly from the client table for reporting purposes.