Searching Record -v- Line Items

[kiwiora]

Hi guys,

This is the first time i've had to use line items and I have a bit of a searching/reporting quandry. I hope I write this so you can understand what I'm trying to get at!

The attached image is a screen shot of my database (don't flame me for the grey colour scheme - it's the clients colour requisite!!).

So two tables lets call them

1. Record Items

2. Line Items

I understand completely that if I want to find information based on Line items - then i go to the line items table and do my search. The issue is, what if the user wants to just search on a field in the record items - lets use field 1 (as shown on screenshot), then whilst I can display that data on the line items table, doing a search in this table would replicate the data x times of the line items (i.e WA would appear 10 times when i only want that record to appear once). So obviously I would want to do this search on the Record Items table and report on this table.

Or for example where I want to search all Volume Rebate Billing types (2 on the screenshot provided) from WA (1 on the screenshot). I don't want the line items reported, just those matters that have a line item matching my search.

How do I switch dynamically between which table to use for which report based on what data the user wants?

And moreover, how do I explain to my users (dimwits at the best of times) that if they want a report displaying all the invoice data they use one search screen -v- if they want just the matter (record) data use another.

TIA

[Sanjai]

I would suggest to keep a listview layout in lineitems table which would show all the searchable fields as columns. When the user search on field1, navigate to this layout, it would show all the records matching field1, when the user clicks on any one of the record, navigate to the screen above.

This would solve your second problem automatically since you are searching in the same table.