Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Reporting on data in a table based on multiple fields


phucbv
 Share

Recommended Posts

I am building a database to allow our company to keep track of all the quotes we issue. I store each quote as a quote header (1 table) and quote lines (in a related table) which are related to the header of the same Quote Ref.

 

I need to produce reports based on the quotes, e.g. display all the quote lines which are in a specified date range or have been issued by a specified employee or belong to a specified product group (product group is a field in quote lines related to the quote header) or on have a particular status (pending or complete). I also would like to be able to report on any combination of these, so perhaps all quotes issued in a date range (with any product group/employee/status etc.) or those ina particular date range that have the status pending (but again on all product groups/employees etc.).

 

Am I being too ambitious or can it be done?

 

The method I am currently trying is to have another table (Report Preferences) which has fields representing all of the search criteria (start date, end date, issued by, product group, status etc.) and then I have relationships to the relevant fields in my quote lines table ('=' for things like employee quoting or product group and '>='/'

 

The only way I've been able to display quote lines based on criteria I enter into the report preferences table is to use a portal. Can I not do it by putting some of the fields from the Report Preferences table in the header and have the quote line info in the body of the layout? (is this a stupid question!?!?)

 

The portal I have created is kind of doing the job but it doesn't allow you to specify all the combinations e.g. it doesn't allow you to display quotes based on date range but over all product groups (no records are displayed in the portal if I leave the product group field empty).

 

Sorry for writing an essay, any help would be most welcome, thanks.

 

Phucbv

Link to comment
Share on other sites

Welcome,

 

For displaying a filtered list on-screen, your method of filtering the relationship is a good way to go. But if it's a complex set of criteria (where the fields used may vary or the criteria should include OR conditions) or the intended result is a printed report, then it's easier and more flexible to use a columnar layout with sub-summary parts based on the child table (the Quote Lines table in this case).

 

For such sub-summary reports, the method of finding the records is just to use FileMaker's Find capabilities, which is quite flexible and easy to constrain or expand.

 

For grouping items, like Quote Lines of the same Quote, use sub-summary parts based on the field you wish to group them on.

 

More details on sub-summary reports can be found in the manual. But let us know if you have questions.

Link to comment
Share on other sites

Ender,

 

Thanks for replying, after reading your post I looked into using find and you are right it is way more flexible than I thought it would be, with some nifty scripting I should be able to achieve my reports in this way.

 

Thanks

Phucbv

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use