re: date to range

[richshone]

I looked more closely at the results of my calculations for the date to range relationship and am sort of getting somewhere with the answers but not knowing the inner workings of this rather complicated calculation I can only see patterns in the answers with no real idea of what they actually mean. In some ways the answers are more confusing than the calculations, as it seems to be something with zeroes and there is no indication that they should be ignored that I can see. When answers to calcs. are text do the zeroes respond the same way?

The paterns I see in the answers may in fact be a red herring, but I am wondering if someone can help me to decide this. I have 165 records related to 6 specific date ranges and of these only 12 are not relating properly. I would love to fix this if possible as it ALMOST works! If someone would be willing I would love to send some data to analyse but I am concerned that it would be too lengthy to do it here. Thanks for any responses...

[BobWeaver]

If you didn't get the smart ranges calculation from the original source, you may have missed out on the theory behind what it does. You won't find a better explanation of how it works than here:

http://www.onegasoft.com/articles/smartranges/index.shtml

[richshone]

Thanks for your comments, as I originally tried the calculations before I knew about the article that explains it fully. Now that I have read it and understand the basic concept a lot better, I wanted to mention that I tried the calculation offered 'with prefix' and it doesn't seem to work! If I put the prefix exactly where it says to, I get a promp that ways that "

[BobWeaver]

I went back and looked at your original formula, and noticed that the Artist prefix field is missing from the last 3 Substitute functions. That would definitely cause the thing to misfire.

[richshone]

So, now I have solved the riddle and would like to share it with all...

Its a strange thing but the equation that I copied from the article on the website didn't work when I TYPED it in, but when I COPIED the differing bit of text from an example file that I had it worked fine - its almost as if there was a difference in Filemaker's interpretation of the characters I typed in (I suspect the " " characters in this case). Don't know what is up with that but it works now so no worries unless you have any thoughts about it (PC vs. MAC for example).

Interestingly enough, although that segment ended up working somewhat, the equation supplied on the website still doesn't work perfectly. That said, once I looked at the example file again (which actually has a functioning prefix_daterange_suffix field result) I was able to finish it successfully by modifying it.

To summarize, to do this you have to add another subtitute text function AROUND the WHOLE equation to get it 100% functional. This I did in the example below, but keep in mind that I also had to change the related datekey field to a prefix_datekey result from the datekey_suffix one it was before. I have checked this in my database and it solves all of the problems I was getting. See below:

prefix_daterange equation (noting the extra line at the beginning and the end):

Substitute(Middle(

Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9",

(Int(Mod(tour_start_date, 10^1)/10^0) + 1) * 2 - 1,

(Int(tour_end_date/10^0) - Int(tour_start_date/10^0) + 1) * 2),

" ","