Specifying relationship to ignore 1

[thanh1800]

I have two database with fields and I have two relationship linking them.

--dumby database--

-table 1-
name
fav_color
fav_day_of_week
address

-table2-
name
fav_color
fav_day_of_week
family_size

--end--

the relationship is
table1::fav_color=table2::fav_color
table1::fav_day_of_week=table2::fav_day_of_week

now sometimes i want to group people with the same fav_day_of_week

and sometimes i want to group people with the same fav_color

and sometime i want to group people with the same fav_day_of_week and fav_color

then i want to display these group
so that all records in these group is displayed on one form/layout/page

note table1 and table2 hold different details of the people
ie one is contact details and the other is personal details.

any surgestions are welcome

 

[thanh1800]

well thanks for your help never the less

and well here is the calender so far
let me know what you think?

http://www.box.net/index.php?rm=box_v2_download_shared_file&file_id=f_17270643

going to add some interactive components to this.

 

[JeanW]

I tried the link but got the message:

User is not allowed to use direct links.

 

[thanh1800]

oh sorry

but i think you'll be please with the calender

http://www.box.net/public/cb64mtbjg7

 

[thanh1800]

ps

this link works
but

click on link
-new window open
click onto the address bar
-the place where you type in web addresses
then press enter
- page refresh with file

for some reason you need to refresh the page via the enter key?

 

[JeanW]

Wow, nice calculations.
However, you let a lot of FM power unused.

Your calc fields can be reduced to f.i.:
Your calenderday:

Let(
[

dateExtended = Extend(date);

monthDay1 = Date ( Month(dateExtended) ; 1 ; Year(dateExtended) );
day0 = monthDay1 - Mod (monthDay1 ; 7 ) - 1;
theDate = day0 + Get ( CalculationRepetitionNumber );
dNum = Day(theDate)

];
//--------------------------------

Case(
theDate = dateExtended; TextSize ( TextStyleAdd(dNum;Bold) ; 12 );
theDate = Get ( CurrentDate ); TextSize ( TextColor(dNum;RGB(0;0;255)) ; 12 );
Month(theDate) = Month(dateExtended); dNum;
TextColor(dNum;RGB(200;200;200))
)

//--------------------------------
)


To fill in the dates you could use a calc along these lines:

Let(
[

dateExtended = Extend(date);

monthDay1 = Date ( Month(dateExtended) ; 1 ; Year(dateExtended) );
day0 = monthDay1 - Mod (monthDay1 ; 7 ) - 1;
theDate = day0 + Get ( CalculationRepetitionNumber );
dNum = Day(theDate)

];
//--------------------------------

Case(
theDate = dateExtended; fill_color[2] ;
theDate = Get ( CurrentDate ); fill_color[1] ;
Month(theDate) = Month(dateExtended); fill_color[1] ;
""
)

//--------------------------------
)

To walk from date to date you need 1 script, triggered with scriptparameters for the days, months and years, something along these lines (for + and - months):

If (Get(Scriptparameter) = "-m"
Set Field (yourTable::date; Let(
monthOld = Month(yourTable::date);
dateNew = Dte(Month(yourTable::date)-1;Day(yourTable::date);Year(yourTable::date))
OK = monthOld <>Month(dateNew);

Case(OK;dateNew;Date(Month(dateNew);0;Year(dateNew))))

Ele If (Get(Scriptparameter) = "+m"
The same, only dateNew + 1

and you do the same for +/- year

HTH

 

[JeanW]

With a few Custom Function you can parse out the first day of the month, the last day of the month, the first day of every week and the last day of every week.

With a simple + or - calculation you will have every dayname of every single day.

You used If() statement embedded in Case() statements.
That's a lot overhead and can start giving problems when you have to use the outcome of your calc in linked files.

 

[thanh1800]

wow thanks JeanW

your method uses is alot more efficient but I havn't mastered all of fmp functions yet so if I did I would have opted for somthing more towards your level of coding.

I am in the process of incorperating your container into the calender so that the backgound changes in acordance to the a 'criteria'

your TextColor works too but I'm afraid I have strict guidelines and must use bgcolor to id important dates(variable)

not that I wont use your TextColor as well, I'll proberly use both

btw how do you set the valign of fields?
I set the halign already
so I want to end with the field content in dead center.

 

[JeanW]

To ID important dates, incorporate them into the Case function. That's the level where you can play, even link a holidayTable to it, or a 'factoryImportantDayTable', even a 'localHistoryImportantDatesTabel'.
Within the Case() yu can link the colors.

Basically you only need 42 rep.field/boxes.
Horizontal the weekdays (7) and vertical the 6 max. possible weeks you can have in a month.

Use also a calc field to show the dayName and the weekNumber, the days on top and the weeknumbers at the left side.

 

[JeanW]

You will see that this gives you a way to 'reverse' calculations also (going back to one of your previous posts).

Out of a given weekNumber (which comes out of a datefield) you can highlight the date in the calender.

With the colorcalc you can highlight a range of dates in the calender.
You can calculate a range of non continual dates to highlight.

Possibilties are endless....

 

[thanh1800]

Well thanks JeanW the new and updated calender is now complete

I used your more efficent code and added the background colour change

overall this prototype can be adapted for use in many situation.

http://www.box.net/public/xf1bd8rm35

I now consider this thread closed
Thanks to JeanW for your help