6 Replies Latest reply on Nov 13, 2009 8:57 AM by jrodilana

    How to change the value of a field depending on the date?

    jrodilana

      Title

      How to change the value of a field depending on the date?

      Post

      Hi!



      I'm new in this forum. I tell you my doubt, I have two related tables, in the first one I have the information of a few equipments (name, model, id, serial number, State...) and in other one what I have it is the State and the from date and end date of this State, the relation is 1:M, because an equipment will be able to happen for many different states.

      I have two questions, the first one, might it be the relation 1:1, because an equipment will be only in a state at the same time?

      And the second question (that worries me more enough) is, how might it do in order that filemaker verifies the date in which we are and modifies the field "State" of the first table (Equipments) depending on the states and the dates of the second table in which we store the states?

      Thank you very much.






        • 1. Re: How to change the value of a field depending on the date?
          philmodjunk
             I can't quite follow your description of this issue. Perhaps you could post an example or two showing the states, dates and results you want?
          • 2. Re: How to change the value of a field depending on the date?
            jrodilana
              

            Ok, I describe you the issue with a example.

             

            Table Equipment                        Table State

            ID_EQ                                      ID_EQ

            Name                                       ID_State

            Model                                      State

            Serial number                            State end's date

            State                                      State start date

            End's of State date

             

            State can take the following values: Warranty, Chargeable, Contract...

             

            In the table state we will have the possibles states that a equipment can take. My issue is, that I don't doing in order that for example once a day, they are verified the dates and change or not the state, it is necessary to bear in mind that we will have many equipments.


            Thank you
            • 3. Re: How to change the value of a field depending on the date?
              philmodjunk
                

              I need to confirm some details here. One record in State would appear to record the period of time that a specific item (equipment) spends in a given state. Am I correct?

               

              If I establish the following relationship:

               

              Equipment::ID_EQ = State::ID_IQ

               

              Then a portal on a layout that is based on Equipment could show all State records that exist for that equipment. The portal can also be sorted by Start Date to display the states in chronological order.

               

              If I make a different relationship:

               

              Equipment::ID_EQ = State::ID_IQ AND

              Equipment::cToday > State::StartDate AND

              Equipment::cToday < State::EndDate

               

              Where cToday is an unstored calculation field: get ( CurrentDate )

              I could create a portal that only shows those state records that are consistent with today's date.

               

              Either of those do what you need?

              (Note: by using two table occurrences for State, you can create both relationships and create both portals on the same or different Equipment layouts.)

              • 4. Re: How to change the value of a field depending on the date?
                jrodilana
                   Thanks, it is very useful, but i don't know how to create the field cToday for to change, every day, for example.
                • 5. Re: How to change the value of a field depending on the date?
                  philmodjunk
                    
                  1. Go to Manage | Database | Fields
                  2. Enter cToday as the field name.
                  3. Select Calculation as the field type.
                  4. Click Create
                  5. Enter Get ( CurrentDate ) as the expression
                  6. Select Date in the result type drop down in lower left corner
                  7. Click the storage options button and select "do not store calculation results".
                  • 6. Re: How to change the value of a field depending on the date?
                    jrodilana
                       Thank you very much, your help me has been of great help.