I have a date calculation as follows:

Get_Current_Date = Get(CurrentDate)

Number_Of_Days = Global field set to 20

Get_Aged_Date = Get_Current_Date - Number_Of_Days

Calculation:

If (Invoices::Invoice_Date ≥ Get_Aged_Date ; Sum ( LineItems::Item_Quantity_In_Stock ) ; "" )

The calculation works fine when there is only one record in LineItems table.

In case of one record only: If I change the date out of range the calculation gets updated as well as when I return it.

In case of two records: If I add a second record the calculation behaves correctly however if I change the first record out of range it returns "" as specified in the calculation but it should return the value of the second record as it is in range. If I return the first record back into range it adds the values for both records.

Do not know where I went wrong - any tips would be appreciated.

Thanks.,

I cannot figure this out, or find how to do it anywhere. Is it at all possible?., would like a simple pointer in the right direction, this is a second time I posted this question - receiving no replies leads me to believe that it is a really stupid questions or the quite opposite.

Here is my file. The fields and the calculation can be found on the layout called ProductsDescription.

Please note that I have been using FM for about 30 days only and many things in my db are probably not like they are supposed to be and I am bound to have some rogue fields and such. A comment on how I am doing and if the direction that I am headed is right or wrong is appreciated as well as the tips on the problem I described in this post.

A-SYS.zip

I think you need to give us better directions as to where this calc is. Is it a script, an autoenter calc, a calculated field? In what table does the calc reside. What fields do they refer to. I'd be happy to take a look at it but need better direction.

Thanks, appreciate it.

The table in which the calc resides is called Products.

The field names are:

GetCurrentDate

HotGlobalField

GetAgedDate

SumQuantityHOt

They can be found at the bottom; just scroll all the way down when looking at the table.

It is a calculation - It references table LineItems as well the field that is referenced in LineItems Table is Item_Quantity_In_Stock

Basically I want to sum Item_Quantity_In_Stock for all records in LineItems table that fall in certain date range.

Date range is specified by calculation IE current date minus 20 days. The range would be between current date and the date that is returned by calculation.

The fields and the setup calculation can be found on ProductsDescription layout. They are 4 fields in the middle.

There are two records in LineItems table. You can modiy those via Invoice Layout. Try changing the first one out of date and back in to observe how the calculation behaves.

Thanks, rally appreciate it.

Forgot., the dates are pulled from invoices table.

If (

Invoices::Invoice_Date ≥ Get_Aged_Date ;

Sum ( LineItems::Item_Quantity_ In_Stock ) ;

""

)

Your calculation is saying: if the invoice date is (ok) then display the sum of all the line items, else display nothing.

It is working correctly.

To get what you want, this calculation needs to be in the line items file, so it works conditionally on each line item. In the invoices file, Sum() the conditional calculated field.

Thanks., that solved it., I can sleep well tonight - thank you.

In my original calculation I assumed that the calculation would check each record to see if the invoice date was ok or not, however it looks like it checked only the first record.

I set up the calculation in line items now and made one more sum field that will sum lineitems sum field and give me a total which is what i wanted to accomplish.

Now I can continue with my db., thanks a lot.