Hi,

I have typical master- 1 - M - <details relationship set up.

I also created another relationship against it to include the above but also based on a condition of a field. For example the field might have "Drum", "Guitar" or "Piano"

I want to count the number of Drum records and Guitar records and Piano records so i came up with this idea to use a relationship and I guess kind of a filter.

I add a record to my first relationship - a new child. But when i get the count from one of my other relationships like DrumRelation or PianoRelation the count is off by one. Is there anything i need to do to force a refresh?

thanks

J__

Try enclosing the Count function within Evaluate.

Evaluate( "Count(yourrelationship::serial)"; triggerfield )

If that doesn't work, stuff and attach the file.

I'm doing this from a script can i still use this?

sorry i'm not that familiar with evaluate - i just looked at the help , it looks very powerful.

serial is where you mean the key correct?

could you elaborate on the triggerfield?

do i need to use that? in a script how would that work, since evaluate wouldn't trigger until script is run.... isn't it optional?

thanks,

sincerely,

J__

Relationship currently is based on a text field. I would like to change all to a numerical key field and base the relationship on that. Is this possible?

TIA

Post deleted by Lee Smith

Sorry, I did not see anywhere to start a new thread on this web page. New to this forum.

Hi, Wizard. It's on the forum main page (see attachment).

Thanks Jim,

Jim has started his own thread here: Wizard's Thread

Lee

J, what does your script look like? It sounded as if you were strictly using a relationship, which is why I suggested the calculation. If you are scripting it, the process may be much easier.

Queue,

thanks so much for the response.

I have a Master - basically a Project, which has associated with it one or more Tasks.

Simple enough. So i use 1-M. Turns out you can specify what type of task it is, I have a drop down where you pick from a list of job types. They can be 3 types D, M, P

and the Task number needs to reflect this. in the format yynnn<jobtype><number>

where yy = year, nnn = number 3 digi number 000-999, jobtype [ D | M | P ] and <number> is 0 to however many jobs of that type are created.

example: 04000D1, 04000D2, 04000M1, 04000P1, 04000P2, so you can look at the number and know what type of job it is. To create these number sequences and they must be in sequence, you must know how many D jobs there are already and how many P and so on.

this is purely to create a visual number, which the user can refer to, but under the hood, my relations, to say customers do not use this number, I use a serial

So, my approach was to create a relation from the project-Tasks, called DJobsRel

with the following criteria:

prj_id -- prj_idFk and

gDConst = jobtype,

job type is the field in the job table which specifies what that the job is D or whatever

I have a global holding a 'const' , gDConst = "D". So, basically this gives me a query of all D records in the table.

I have one of these for each job type.

I then (in a script) when the new job needs to be created with this special id, which is based on the drop down they pick in the job... i do something like this

if [jobs::jobtype = "D"] /* D job type */

Set Field [jobs::job_id; jobs::prj_id_fk & "D" & GetAsText( Count( DJobRel::uid) +1)]

end if

if [jobs::jobtype = "D"] /* D job type */

Set Field [jobs::job_id; jobs::prj_id_fk & "D" & GetAsText( Count( MJobRel::uid) +1)]

end if

my jobs::prj_id_fk is already something like 04nnn, so I get when i concatenate

04001D1

04002D2

04003M1

04003M2

i show them in a portal and i sort them so they are grouped together in the portal.

it looks to me like sometimes i when i do count, the count is behind and so I'll get 2 numbers with D1 in them. That's what the refreshing was about.

sorry for the length of post, hope that makes it clearer.

sincerely,

j__

See if the attachment works for your purposes. It doesn't require any scripts or globals, just an extra relationship in the Tasks table.

Note that once a Type is selected, the record must be committed (you have to click out of the portal or press Enter) before the next related record can be created correctly.

J_Projects_Tasks.zip

Queue,

thanks for the sample, I appreciate it very much. This has some good points to it. I'll try to absorb it.

thanks again so much.

sincerely,

J__

Queue -

I'm getting that refreshing problem again. How can I do this with a Script?

thanks,

Sincerely,

J__

You mean how can you auto-commit records after adding or changing the Type? You could probably use EventScript to trigger a Commit Records script. You could also change the menu to be a list and attach it to a script that runs a loop, waiting for a selection, then Commits the request. See here for a discussion and some sample scripts about triggering a script using a pop-up list on a Mac.

Queue - right that is what i have been trying to do. I actually read through your link and your articles/discussions.

I wound up putting a global in the Master table (Projects) which does a count ( jobRel::uid )

although i do not display this global anywhere, i have it set to be unstored, wouldn't this force the relation to refresh maybe?

It seems like this works ... so far... what do you think?

I did try putting the script ( CreateID ) under that drop down, but the count was a little weird.. but i was a little tired, so it maybe ok now.. have to revisit.

thanks!

sincerely

J__