date range mind twist

[richshone]

I have a brain tease for people out there. I have a calculation/relationship that allows me to have a relationship between two files using a date range to define which record to go to. Most of the times it works, but there are some dates that don't show up as related for some reason. Either there is a flaw in the calculations below or Filemaker is having trouble with it in some way. For now I wish to assume that it is a flaw in the calc. If anyone can see this flaw can you tell me where to look? Much appreciated.

file 1 (tours):

I have tour start date, tour end date, and artist. Then I have this calc for artist_range:

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), " ",Artist & "

[falkaholic]

I tried setting up that same trick, and also with a twist.

I was trying to do 2 portals with dynamic keys and i failed. The only person that really understands that trick is the author. Because to me it seemed like it should work.

My suggestions, make some fields that dump the answers of the calculations and see if they seem right.

[BobWeaver]

Eric's right. The best way to debug these calculations is to have a look at the actual calculation result that you are getting. The smart ranges formula is very complex, so it's easy to make a typo somewhere.

Also:

1. Check to see what your calculation result type is. As I recall, it should be text for these formulae.

2. What does the artist field contain? A unique artist ID (good), or the artist's actual name possibly with embedded spaces? ( a bad, bad thing!)

[richshone]

Thanks for your answers. Two things you make good points about: Firt I will try to look at the results of the calcs to see if the answers are what I need. Second, the artist fields are in fact simply a text field containing the artist name, drawn from a value list (in my mind thinking that a value list would eliminate errors - spacing etc.). It really never occured to me to have a unique ID for them although I have unique IDs for other things - is it too late to modify my app for this purpose? Any ideas as to how to proceed safely? Thanks...