set expiration on jan 01

[daveealex]

I am trying to make a field that is a expiration date field change to red(I can do this in conditional formation already, but i don't know how to set the jan 01), then at the same time I need another field that will change from active to expired(and become red) when the current date arrives to jan 01 2010(this needs to be done every year), Then when I go back and change the expired to active and the date field to jan 01 (ex. jan 01 2011) of the next year everything rests for jan 01 2011(and so on). Every person will have a different start date so i can not tell the calculation to expire in X days. I hope this is clear.

Thank you in advance

[comment]

Try:

Year ( Get(CurrentDate) ) > Year ( StartDate )

Note:

I am not sure what you're doing here - perhaps you should consider creating a new child record for every period.

[daveealex]

Let me make an example, that will help. Joe comes to me on March 01 2009 and asks me to buy a travel card. I create a new record for Joe(if he does not have one already) and in the field 'Status' i make him Active. Susan comes to me on Dec 01 2009 and buys a travel card. I create or update her record with active. Now I need both of these active members to "Expire" in the 'Status' field changing to Expired on Jan 01 2010. Then the cycle needs to repeat, Joe comes to me on February 01 2010 and buys a card.....and so on. I hope that clears it up. Thank you in advance

[comment]

I believe it should work like this:

Joe comes to you on March 01 2009 and asks to buy a travel card. You create a new record for Joe in the Customers table (if he does not have one already), and in the related Cards table you create a new record with March 1, 2009 in the IssuedDate field.

Susan comes to you on Dec 1, 2009 and buys a travel card. You create a customer record for her (if it doesn't exist) and a new card record starting on Dec 1, 2009.

The current status of a customer would be calculated (in the Customers table), for example by =

Year ( Get(CurrentDate) ) ≤ Year ( Max ( Cards::IssuedDate ) )

This returns true if customer has an active card, false otherwise.

This way you'll have a trail of customers' history - rather than erasing older data with new, as your method would.

[daveealex]

Thank you comment. So I will need to create another table called cards with a foreign key linked to my customer table. In this table i will put in all the info about the card related to the customer, then place it into the customers table with something like a portal. Am i understanding this correctly? If not where does it go? I am still learning filemaker, I do apologize.

[comment]

That's exactly right - see the attached demo.

I am still learning filemaker, I do apologize.

No need to apologize - that's what the forum is all about.

Cards.fp7.zip

[daveealex]

Thank you so much Comment!!! I will recreate(reverse engineer to learn how you did it first) your sample and integrate it into my project. The only thing I don't understand is where the active and expired come from. I know that boolean 0 is connected to expired and 1 is active (yes and no). How are these two cstatus boxes connected? Is it hardwired in FileMaker? I can see the notes you have placed next to the active/expired box, i have checked the calculation in the table itself, i am just missing it. Also i need to be able to change the active to activo and the expired to scaduto because I work for an Italian company. I can leave them in English if needs be because when the search the can search for a 1 or a 0 but the Italian would be a little better if possible.

Thank you again Comment.

[comment]

i have checked the calculation in the table itself, i am just missing it.

It's not the calculation - it's the way the field is formatted on the layout (you can see another instance of the SAME field that displays as 0/1). In Layout mode, select the field and choose Format > Number… from the menu.

[daveealex]

Thank you again Comment. I now see it. Very nice place to hide it. I now understand(even more) that the cStatus boxes are the exact same thing, just 2 different expressions of the same thing. Not finding or even knowing this is due to my lack of training. Need to take some courses this coming year. I am going to start with the FileMaker pro 10 missing manual to get me started. Thank you again.

Edited November 25, 2009 by Guest