Jump to content
Sign in to follow this  
MLink

Finding records based on date

Recommended Posts

Hi everyone,

Before I ask this please bear in mind I am so far completely self-taught in FM and so there is a very good chance this is a very stupid question.

I have a table called Projects. I have a related table with invoices in it. I need to make a total of all the invoices from a project, in each of the 12 months. So one for February, one for March, etc.

However I can't figure out an easy way to do this. It's just a matter of something that goes through the table of invoices, checks the month, and if it's "1" adds it into the "Total Invoices for Jan" field, for example.

I'm sure there must be an incredibly easy way to do this, but the only way I can think of is filtering all the invoice data by creating 12 new instances of the Invoices table on the relationship graph and then matching them.

Can anybody help me out, or at least point me in the direction of the fundamental resources I am missing?

Thanks guys and gals, all replies welcome.

Share this post


Link to post
Share on other sites

It's actually not that easy to do this.

It's possible to do it using scripts and/or relationships as you suggest, but before you go that way, I suggest you start here:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html

Share this post


Link to post
Share on other sites

Wouldn't a Summary report work?

Create a calc field in the Invoices table that gives you a working Month/Year combo for the Invoice Date. Something like

= Right(0 & Month(Date);2) & Year(Date)

Then create a Summary field which is the Sum of your Invoice totals.

On a report layout, add a Subsummary field when sorted by your calc'd MonthYear field. Add your new Sum field Sort by MonthYear and Preview.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

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.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.