6 Replies Latest reply on Apr 1, 2014 10:14 AM by fabiuz

    Calculation field, Index and Value Lists: how to have them work togheter?

    fabiuz

      Title

      Calculation field, Index and Value Lists: how to have them work togheter?

      Post

           Hi,

           I have one table that I use to create a value list, the fields are:

           id, startDate, calculatedDateOne, calculatedDateTwo

            

           startDate is a date field that stores the same date for all rows in table. Indexing is set to none and the option "Automatically create indexes when needed" is activated;

           calculatedDateOne is a calculation field that uses the startDate field to calculate a new date value. Index options are the same as startDate field.

           calculatedDateTwo is a calculation field that uses get(CurrentDate) and the calculatedDateOne to obtain a new/final date value. Index options are the same.

            

           What I need to do is to use these table fields to create a Value List for a dropdown field in a layout: so to create the value list I select "use values from field option", I use the id as the first field and I use the CalculatedDateTwo as the second field.

           That seems to work and do what I suppose to get. The problem is that the calculation fields are not updated when the current date changes. The Value list is always the same obtained when the index is created the first time.

           I cannot remove the index and I cannot set the calculation fields not to store the index because the Value List does not work anymore due to missing index. So I have a value list that is not updated real time.

           Things are even worse if I try to set the startDate field to be a Global Storage: Value Lists do not accept calculation fields that use a Global Storage value.

           Does anybody have a suggestion about how to fix this and have a real time Value List for my layouts?

            

           Thanks a lot!

        • 1. Re: Calculation field, Index and Value Lists: how to have them work togheter?
          philmodjunk
               

                    startDate is a date field that stores the same date for all rows in table. Indexing is set to none and the option "Automatically create indexes when needed" is activated;

               This will not be the same date for all rows in a table unless you specify global storage. If you select that option, indexing is disabled for the field as all records in the table show the same value in a global field.

               

                    The problem is that the calculation fields are not updated when the current date changes.

               Why would the current date ever change? I don't see anything here that refers to the current date in the first place.

               Let's back up a bit and try a new description.

               First, please describe the purpose behind what you are attempting to set up. Forget fields and tables for a moment and just tell me what you want this part of the system to do.

               Then explain the calculations for CalculatedDateOne. Is this a date field with an auto-entered calculation or a field of type calculation? What is the calculation expression that you are using for it.

               DoesCalculatedDateTwo even get used in this process? (You only mention it as one of the fields in the table and then say nothing more about it.)

          • 2. Re: Calculation field, Index and Value Lists: how to have them work togheter?
            fabiuz

                 Thanks for the quick reply,

                  

                 Suppose I have 100 costumers: I have to visit them once a month, and I have to take note of the day when I usually visit them.

                 My month is made of four weeks and goes from Monday to Friday. 28 days only.

                 So I have set a "Visits" table and each costumer could have one or more days in the month that I could use to visit them.

                 So I have VisitDay1, VisitDay2 ..... VisitDay20.

                 Since VisitDay13 does mean nothing to me and I have to take in mind to which VisitDay I am and which could be the next visitDay, I have set a starting Date that I use to calculate which is the next VisitDay by showing the corresponding date.

                 I want to see next visit days date instead of VisitDay1 VisitDay 2 etc. in the value list when I search for costumers.

                 So I have made one more table that contains VisitDay Ids, their name "VisitDate1, 2 3..." and for each of them I try to calculate the next valid date based on a fixed starting date and current date.

                 Since I cannot use a Global Field to make calculations that are used in a Value List, I have manually set the same starting date for each record (startDate field).

                 Then I have created a first calculation field that does this:

                 startDate + (28 * Floor( (Get ( CurrentDate ) - startDate) / 28 ) )  + VisitDayPosition + 28

                 where visitDayPosition is the VisitDay position in table: VisitDay1 = position 0, VisitDay2 = position 1, VisitDay6 = POSITION 7, VisitDay 20 = position 25. So I take care of saturdays and sundays in between.

                 By using such calculation I can get the next date that I will repeat the visit to the costumer.

                 But I want to start to show dates from the current date, so I have to check even if the current day is greater the next VisitDay - 28days and return the right result so that I can have dates starting from today and go ahead till next 28 days.

                 So the second date field I do this:

                 If ( Get ( CurrentDate )  >  calculatedDateOne - 28 ; 
            calculatedDateOne ; 
                 (calculatedDateOne - 28)
                 )
                  
                 Once I have all this informations available I want to use them to fill a ValueList in a Layout so that I can easily select the VisitDay I want to use. Now it is working this way but the date is not updated. I have to update indexes to see them correct each day.
                  
                 Hope my explanation is comprehensible!
            • 3. Re: Calculation field, Index and Value Lists: how to have them work togheter?
              philmodjunk
                   

                        So I have set a "Visits" table and each costumer could have one or more days in the month that I could use to visit them.

                   

                        So I have VisitDay1, VisitDay2 ..... VisitDay20.

                   That seems a very inefficient design for this. I'd make each visit to a customer a different record in a table of visits. With just a single date field in each to record the date of the visit.

                   Customers------<Visits

                   Customers::__pkCustomerID = Visits::_fkCustomerID

                   This makes it possible to display all the visits for a given customer in a portal on a customer layout. And this portal could be used to log new visits to that customer.

                   And if your planned and actual dates for a visit differ (you or the customer had to reschedule), you can have two dates in that record, one for the planned visit date and one for the actual visit date.

                   And a calculation can be used with a script to create a new visit record for a customer and to enter the scheduled date of that visit into the appropriate field.

              • 4. Re: Calculation field, Index and Value Lists: how to have them work togheter?
                fabiuz

                     Unfortunatly my english explanation was not good,

                     You are correct, I already have a dedicated table where I save and take trace of each single visit.

                     What I am trying to do here is to plan my next visit and to assign each costumer to one or more "VisitDay" that will repeat every 28 days.

                     So for example Costumer15 can be visited on VisitDay3 or VisitDay4 and that will repeat after 28 days.

                     I want to be able to say "On April 15th I will be here and here..." without the need to calculate by my self to which VisitDay it will correspond.

                      

                     With this additional information I hope you can better understand what I mean in my previous reply: I am calculating to which VisitDay number and to which assigned costumers, each day of the month will correspond.

                     So in my Value list I will have:

                     2014-04-02 = Visit Day 18 and these are costumers assigned to such day.

                     The same should happen for each day till I have completed the 28th day of visists. I will not consider saturdays and sundays.

                      

                     thanks

                • 5. Re: Calculation field, Index and Value Lists: how to have them work togheter?
                  philmodjunk

                       But I still don't see the need for all of these additional fields. You can schedule an upcoming visit date by creating a new record in your visits table. As I said in my last post, one field can record the scheduled date of the visit and one can record the date of the actual visit. A calendar layout can be set up to show either scheduled or actual visits as can a list or table view report where you can perform a find for all scheduled visits for a day or a range of days.

                       And to set up a "standard schedule" of 1 or more visits on day 1 through 28 really only needs a small related table where you set up one record for each visit in that 28 day interval where you need only record the number of the day in a number field. This should make for much simpler calculations when you set up a script to schedule the "next visit" for a given customer.

                  • 6. Re: Calculation field, Index and Value Lists: how to have them work togheter?
                    fabiuz

                         mhm... right yes, but I think I have explained just a little piece of variables and of my db.

                         Each costumer have a different interval of time between each visit, in the mean time he could require a visit earlier or he could make a phone order or he cuold be closed for some days.

                         What I am trying to achieve is the ability to see which costumers could be visited in one day of the month and I will also be able to select which one to visit (this depends of many factors). I have to have a list of all the costumer I could visit and I select which to really visit.

                         For the costumers I discard, the interval of time will still be valid for the next time even if I do not visit them and I have to know when it would be possibile the next visit: may be day13 and day22 .

                         For reasons like these I have setup a "turn day" and I have assigne one or more then one to each costumer so that I can immediately know when I could visit them.

                         In my layout I will be able to know which are all the potential costumer I can Visit on a date and this is why I am settings things in this way.

                         All this is already working by using dayNumber index, what I am trying to do now is to substitute dayNumeber with corresponding real date.

                         All calculations do the job but they are not updating results once the index is created.

                         What I want to do is to update the index for calculation date every day or when current date changes at last.

                         I was not aware of this limits of FM with Global Fields and indexes to have ValueList working, it was done already elsewhere.

                         Do you know if it somehow possible force an indexs update for a a table?

                         thanks!