Calculation help on one year date

[cterrell16]

I'm trying to get a calculation to tell me if a pricedate is over one year old.

Reason if a price date is older than one year we have to update the price. Currently we have to look back to our part index everytime. I'm wanting the calcualtion function to get the date and give me a yes or no if the date is older than one year.

Can anyone help me on this calculation.

I was thinking something like this to start with.

If(SubWorkOrder::PRICEDATE1) < (Less than one year here) "NO" ; "YES" )

[steigrafx]

Try:

If (Get (CurrentDate) - SubWorkOrder::PRICEDATE1 > 365 ; "Yes" ; "No" )

Or, instead of using it in a field, use it to conditionally format the PRICEDATE1 field to turn it red as a visual indication that the price is more than one year old.

Kevin

[steigrafx]

I've attached an example for you.

test.zip

[comment]

365 days is not always exactly a year - though I suppose it will do in this case. However, the calculation field must be unstored otherwise it will not update as days go by.

BTW, the Yes/No part is redundant - see:

http://fmforums.com/forum/topic/47570-set-a-field-based-on-portal-content/page__p__222189#entry222189

[cterrell16]

That calculation worked great. Thanks.

What if in the same instance I didn't have a price in my system for a particular part number. What if I wanted it to tell me it was brand new. What could I add to the calculation to make it tell me that also.

[Aussie John]

That calculation worked great. Thanks.

What if in the same instance I didn't have a price in my system for a particular part number. What if I wanted it to tell me it was brand new. What could I add to the calculation to make it tell me that also.

You would need to start a nesting structure of your if statements but in this instance it might be better to use the similar but easier to use "case" function

Assuming the definition of Brand new might be less than 31 days old? a calc might be;

Case(isempty(SubWorkOrder::PRICE),"Some text",

Get (CurrentDate) - SubWorkOrder::PRICEDATE1 < 31, "New item",

Get (CurrentDate) - SubWorkOrder::PRICEDATE1 > 365 ; "Yes" ;

"No" )

(calc not tested)

If the price is empty it will only test that and ignore the remainder.

cheers

[cterrell16]

That doesn't seem to work.

Lets say for instance when I type in a part number that is in my current system, filemaker will pull from the part index and put the part name, process and price in my work order for me. If the part number is not in my system it will not put that info on my work order.

Can I some how make a calculation that states when I don't have a price in my system it will state that it's new.

I was looking at the conditional formatting. Maybe that is where I would use this. If a field is empty I want it to shade that field. I was reading a few other posts and it had this for an empty field. text not IsEmpty( Self ). I tried it but it did not work. Any ideas.

[Aussie John]

That doesn't seem to work.

Lets say for instance when I type in a part number that is in my current system, filemaker will pull from the part index and put the part name, process and price in my work order for me. If the part number is not in my system it will not put that info on my work order.

Can I some how make a calculation that states when I don't have a price in my system it will state that it's new.

I was looking at the conditional formatting. Maybe that is where I would use this. If a field is empty I want it to shade that field. I was reading a few other posts and it had this for an empty field. text not IsEmpty( Self ). I tried it but it did not work. Any ideas.

I think you need to be a bit clearer how data is stored (and tabled). But regardless if you are looking up another table to enter your data the calculation for your current table should work so long as the correct data is looked up. Try it line by line to trouble shoot. ie what part doesnt work? Are you adding a space or hidden character?

[cterrell16]

I think the reason it's not working is because if a part is brand new it will not display anything into the cell. So your program used <31 days. I guess because it's blank it doesn't know what to do.

Any ideas.

[Aussie John]

I think the reason it's not working is because if a part is brand new it will not display anything into the cell. So your program used <31 days. I guess because it's blank it doesn't know what to do.

Any ideas.

Yes if the cell is blank no text will result. Would this work?

Case(isempty(SubWorkOrder::PRICE),"Some text",

Get (CurrentDate) - SubWorkOrder::PRICEDATE1 < 31 OR isempty(SubWorkOrder::PRICEDAT1), "New item",

Get (CurrentDate) - SubWorkOrder::PRICEDATE1 > 365 ; "Yes" ;

"No" )