×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Specifying relationship to ignore

Specifying relationship to ignore

Specifying relationship to ignore

(OP)
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

RE: Specifying relationship to ignore

(OP)
also how do you increment a global variable($$counter) by 1
eg

---code--
$$counter = $$counter+1
--end code--

RE: Specifying relationship to ignore

(OP)
nope actually i figured it out
Set Variable [$$counter; Value:$$counter + 1]

but can you increment the global variable by 1 without using a script?

RE: Specifying relationship to ignore

I can't see why you have 2 databases for the data.
Or do you mean 2 tables. It's not very clear, you mix the two items.

I suppose you use one database and two tables.
Put the data in one table and use tabs to divide the different items.

With some self-join relationships you can bring the info together in a layout within the same table.

Use the filterkey technique, so you can make choices and combinations.

Set field is only available in ScriptMaker.

RE: Specifying relationship to ignore

(OP)
Thanks JeanW

yes I ment to say tables.

I now need to be able to retieve values in records to use in calculations

eg.

name fav_color fav_animal Value Global
kate       red          dog         1      12
jan         blue        dog          1      12
john       red          cat         10     12

so in this sample I want to retrieve values from the field Value and add them together

--code--

for(index = 0; index < last; index++){
$$global = $$global + Value[index]
}

--end code--

if someone can tell me how to retrieve values from specific records in a table it i be much appreciated

RE: Specifying relationship to ignore

(OP)
Nope I've just figured it out thanks
GetNthRecord ( Value; Index)

but can you do it with global variables?

RE: Specifying relationship to ignore

The $ symbol references a local variable and two $$ symbols reference a global variable. An optional repetition number appears in square brackets [ ] immediately after the variable name.

RE: Specifying relationship to ignore

(OP)
ahh yes

now how to set a field row to a certain value?(reverse operation of the previous question)

eg.

--code--

set(Value;Index) = 20

--end code--

RE: Specifying relationship to ignore

I don-t know what a field row is.
It doesn-t exist in FileMaker.

RE: Specifying relationship to ignore

(OP)
I mean
I want to specify the record to recieve the value

so eg.
field name = Value
record = 3

which currently holds the value of 10

now i want to specify the value to 20 so the dummy table will look like...

name fav_color fav_animal Value Global
kate       red          dog         1      12
jan         blue        dog          1      12
john       red          cat         20     12

RE: Specifying relationship to ignore

(OP)
--code--

set(field name;record ) = 20

--end code--

RE: Specifying relationship to ignore

Use the relationship based upon the recordnr to set the field to the value.

Set Field [<table::field>; <value or formula>]

This can go only within a script.

RE: Specifying relationship to ignore

(OP)
what is recordnr?

RE: Specifying relationship to ignore

Deciding what fields are common to all databases projects is a personal preference related to the way you design databases.
There are fields that always seem to be added at some point in the life of a database.
One if them is recordnr. or serialnr.

This field, as auto-enter serial number, is the most common method for creating relationships between files/tables.
It also provide an effective way to uniquely identify each record in the database.

RE: Specifying relationship to ignore

(OP)
so how would I use a recordnr to set the field("Value") in a specified record(#3) to the value 20?

and also is it posible to change the bacground color of a button, rectangle, layout etc... with script or something without having to set it manually in design mode?

RE: Specifying relationship to ignore

Depend on how you wan to do it.

A simple set field script that act within the same table is no problem.
The moment you want to reach a field in another table/file, you need a way to reach that field.
One way to reach the record and the field can be a relationship, not necessarely, depend on what you want to do.
You can simply reach the record/field in the first part of the scriptstep.

Your syntax will always be : Set Field [<table::field>; <value or formula>]

The specified field doesn't have to be on the current layout.
 
 If the result of the calculation doesn't match the target field type, and the validate option for the field is set to Always, the field will not be set and an error code is returned (which can be captured with the Get(LastError) function)
 
 When possible, the Set Field script step makes the record active and leaves it active until the record is exited or committed. Scripts that use a series of Set Field script steps should group these steps together if possible, so that subsequent Set Field script steps can act on the record without having to lock the record, download and upload data, index the field, and so on, after each individual Set Field script step. These functions and record level validation are performed after the record has been exited or committed.
 
For the color question. A possible way to go:
Make a set of global container fields (in your developerstable f.i.) and a global repeat container field to hold the different colors
If you put the global container behind a transparent field (whatever this is) you can change the color simply by drag and drop the desired color from the repeating field to the color container.
This can be done in a 'preference' layout.

RE: Specifying relationship to ignore

Another option for the background is a calculation field, result container.
The calculation holds the conditions for the colors.

For the textcolor you could use one of the new functions in FM.

RE: Specifying relationship to ignore

(OP)
yes i need my set field[] script to act on a table that is no the current active table.
well this is what I am doing
Set Field [<dummytable::Value>; <20>]

it works but the '20' only apears in the current active record.

I need to be able to specify the record(3) the 20 is to apear in.

RE: Specifying relationship to ignore

(OP)
and thanks you container idea might just work well for me

but feel free to throw some more idea into th mix

RE: Specifying relationship to ignore

That's where you have to target your record first.
One way to do that is with a relationship with what you will be able to reach the record.

And for the ideas, I make a living developing applications, so, it's not because I'm a chef in a restaurant that I have to give food away for free at everyone who's hungry.

I want to help (if I can), not only you, also all the members on the forum. But not in dept and always with every single detail.

I want to show you the way, the direction to follow, the place where you can find the (a) solution.
And not knowing the structure or the design of your application, I, and everyone else, can only do that, nothing more.

HTH

RE: Specifying relationship to ignore

I tried the link but got the message:

User is not allowed to use direct links.

RE: Specifying relationship to ignore

(OP)
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?

RE: Specifying relationship to ignore

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


RE: Specifying relationship to ignore

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.

RE: Specifying relationship to ignore

(OP)
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.

RE: Specifying relationship to ignore

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.

RE: Specifying relationship to ignore

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....

RE: Specifying relationship to ignore

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close