Limiting the set of records from a relationship

[jhotz]

I need to use both the full set of records from a relationship and also a subset of them in a month-end report.

I'm developing an inventory and POS database, which has progressed thanks to this forum (and of course the people on the forum), and I'm working on the month-end report for sales (inventory reduction) and orders (inventory increases). Each of these are separate files, along with the reports file, and they are related using a date range (thanks CobaltSky for the date range example). What I need is to have the total for the month, and the fiscal year-to-date total on the same form (report). I can either get the records for the month OR for the fiscal year, but I can't seem to figure out how to get the records for the fiscal year (for the YTD total) and then only use some of them for the month total.

I had one thought of using a running total from month-to-month to get the YTD total, but I'd need to put in some kind of "new year" check so that the totals start over again (the FY does not correspond to a calendar year). This doesn't seem like the best solution.

Mentally I can put together the logic, like "if date is in range, then use the record as part of the total". But, I can't figure out how to write this in "FileMakerPro logic", or even if this is really the correct way to handle this.

[Ugo DI LUCA]

Hi,

If I understood, each record has cFiscalYear and a cMonth indexed calcs calculation, from which you can create the "OR" relationship.

Why not concanate these 2 calcs in a new MonthInFiscalYear so that you can perform the "AND" grouping for each month and get the Fiscal Year from the previous cFiscalYear.

BTW, as this came up a lot these last 2 days, you may even use a calculaton

cMkey =

FiscalYear&"

[jhotz]

Ugo, I'm not sure if I'm following. Are you saying that I should use the concatanated field as the key for the relationship? Also, I'm not really familiar with the

purchase.zip

[Ugo DI LUCA]

Hi,

Well the "

[jhotz]

Ok, I'm sure that I tried creating two relationships before and it didn't work. However, now when I post the problem to the forum, it decides that it's going to work. It's probably more likely that I did something different last time.

I created one relationship using the date range for the month (cRange_key = :purchaseDate_key) and one relationship using the FY date range (cRange_KeyFY = : purchaseDate_key). Now the total displays properly for both the month and the FY. Like I said, I'm sure I tried this and it didn't work, but at least it's working now.

Thanks for your time and help, Ugo.

[jhotz]

Ok, I've got it working for most of the fields on the report, but I've run into a similar problem with the fields that show data from the Money Transfer file. This file stores a series of miscellaneous financial transactions (donations, supplies, copies, etc.). Each record has a Type and an Amount. On the report, I need to show the summary for each Type (some types are expenditure, some are income), but I'm not sure how to either limit the full set of records, or to setup a different related field that combines a range and another value.

This may be a basic question, but should I setup a relationship so that only the records I need are retrieved (or really related to), or I should I get a larger set of records and then limit them somehow?

I figured that for the month summary, I could just use a related field like "Month & Year & Type" which would avoid the range problem. But for the YTD, I would still need a range (our Fiscal Year is not equal to the calendar year) of values. Part of my confusion is that I did not create the date range calculation, and so I'm not very familiar with how it works (see my first post). I believe I need a concatenated field in the reports file like:

02 2004 donation

03 2004 donation

04 2004 donation

...