Indexing error

[jacobhb]

The situation: I have two tables in a relationship together linked by a "date" field. Table 1 has a portal in it linking to the "date" field in table 2. The date field in table two is a calculation that does not store itself. It is simply a Get(CurrentDate).

The problem: I have realized that you will always get an index error when you link via a unstored calculation. So here is the problem... how would I get "date" in table 2 to change whenever the date changes.

What I have done: I have tried a "start-up script" to set the date but it simply doesnt work. Another issue I have is the fact that table 2 is instanced 37 times.

Thanks,

Jacob

[comment]

Why not leave date empty and add a flag field, AlwaysCurrent.

Concatenate date and AlwaysCurrent in another field = date & "

[Ender]

Hi Jacob,

As you have figured out, you can't index a field that uses Get(CurrentDate). This means this field is not a good candidate for a match field. Even if you used a script to update the date in all records, it is a poor soluton to the problem.

There is likely another way to do the relationship by putting the get(CurrentDate) part of the relationship in the other table, and keep your match fields simple dates. If you can describe what's going on between these two tables in more detail, I can be more specific.

[jacobhb]

I am going to try to explain it as best I can. (If this fails, I guess I can just post the file). I somewhat miss explained the first time I tried so I hope this works better this time.

Table 1: is called "Main." "Main" holds the fields "CalendarDate1" - "CalendarDate37." "CalendarDate1" - "CalendarDate37" are place holders for all the possible date/day combinations in a year (for an example just look at Jan. of this year. While there are 31 days in Jan, the 1st of January was not until a Saturday. Therefore you would need 37 place holders to make a calendar that can fluctuate with the years.

S M T W T F S

- - - - - - x

x x x x x x x

x x x x x x x

x x x x x x x

x x x x x x x

x

[comment]

What is the orange box, and how is it displayed? Do I understand correctly that there are also 37 container fields in Table 1?

[Ender]

In this case, the Date field in the Date table should be the date of the event. Usually calendar modules are not based on the current date, but on the month and year of the calendar record. This involves 42 parent keys and relationships to the Dates, where the first key is the first day of the month, the second key is the second day, etc. If you only allow one Date event per calendar day, this can be simplified further with FM7's ability to start portals on different rows (one relationship with 42 portals.)

You should look around for examples of calendar modules, as they may help you understand how it all works.

[jacobhb]

The calendar I made is not based on the current date... I am just trying to get an orange "box" to appear in the current date. The calendar I have now works perfectly. I made it from scratch because I didn't want to use a 3rd party one mostly because I wanted to be able to understand the inner workings of it all.

Comment- no, there are 37 calculation fields in Table 1. These calculation fields hodl the date. I have made a box and put each "CalendarDate" in the top right of the box essentially making it look like a calendar. Now what I was trying to do is put a portal in the top left of the box where the field in the portal has the same color background and text color. So where there is a relationship, then it would appear as an orange box. And there will only be a relationship when the today's date is equivalent to one of the 37 "CalendarDate"

I have thought of some hardcoding ideas that could possibly work... I will try it tonight.

[comment]

I am sorrry, but I still don't get it. Perhaps you should post the file.

[Ugo DI LUCA]

Hi,

You currently don't need a relationship for this, but it would be of bad advice to tell you'd need 37 additional calculated fields to your current 37 calcs when a calendar could run with just 4 to 5 fields for the very common ones...

Take a look at this one, as I think it shouldn't be that hard to dissect and implement. I'm not saying you're wrong to try it your own, that's how you obviously learn much, but sometimes, it's worth taking a look rather than hitting the walls

calendriers.zip

[Ender]

Yes, this can be done pretty easily with repeating fields (I'm glad you brought this option up, Ugo.)

I think the main reason for using a separate table for Dates, is if you want to look at the same set (or subset) of Dates in a different format. Like having one layout as a Monthly Overview, one layout as a Weekly View, one layout as a Daily Planner. I'm not sure what kind of ugliness would be involved to make this happen with repeating fields, but this is not too difficult with relationships.

[Ugo DI LUCA]

Hi Mike,

Actually yes, one would use a repeating calendar for just the purpose of displaying an infinite calendar, but IMO, when it starts involving appointments and the such, of course, the repeating format may happen to not be the best choice vs a relational design to a Planner db, specially when dealing with a mix of personal agendas.

As a matter of fact, I'm using a mix of repeaters and relationships here myself, but surely not 37 calculated fields either.

The Daily Planner, for its displaying part, involve repeaters for each hour blocks and is quite complex I admit as it gets scripted anytime a day is selected.

The others are based upon a Calendar File updated automatically to account for a new set of days and years, and reflects the users and company days offs, and some preferences as well.

HTH

[jacobhb]

I managed to get it working. I really don't know why it works as it does (I mean I understand it, but I don't get why I had to do it this way).

I choose not to use repeating fields as it is a planner with a month view, a week view, and a day view.