count in relationship sometimes off why?

[J__]

Hi,

I have 2 tables Project -> Tasks, joining the two tables by the project_fk in Tasks. Works fine.

the 'interesting' part (for me at least) is that when a user creates a Task record, they want a unique number (call nice number) based on a concatenation of the project Id and the first letter they pick from a drop down list (Statustypes).

The task records related to a project can be 3 different status types. Open, Pending and Closed (in a value list popup menu) [called StatusListTypes. For bookkeeping, it's required that a number be generated encoding the first letter for each task - just for reference - like part numbers.

so if I pick "Open" from the drop down list when I create a task, get a unique number based on a concatenation of the project id , first letter of the task statuslist item selected and the count+1 it is.

example:

I have a project called 001 with 4 tasks.

tasks ref number are: 001-C1, 001-C2, 001-P1, 001-P2. When I create my next record with status list selected "Closed", I get 001-C3

To get this to work i used a self-join on the Tasks table with 2 globals in the Tasks table, gProjectFK and gStatusListSel, which is joined in the relation to tasks::proj_fk and tasks::statusLIst respectively. The relation is called tasksStatusRel

so in my script when I create a new task, i simply set the two globals

set field [ tasks::gStatusLIstSel ; tasks::statusList ]

set field [ tasks::gProjFK, tasks::proj_fk ]

and then say

Count ( tasksStatusRel ) + 1 to get the next number.

and just concatenate

set field [tasks::refNum; GetAsText (tasks::proj_fk) & "-" & Left(statusListType; 1) & GetAsText (Count (TasksStatusRel) + 1)

I tested this extensively on fm7 dev and it seemed very reliable. The user has it running on a server fm7 v1 on OSX and another user opened (remotely) the database and claimed that the Closed produced 2 for the first record. So , basically skipped, thereby skipping 1.

any thoughts on why this relation might not be reliable or working right? Do I need to do some kind of refresh? I thought that setting the globals would force a refresh on the relation and give me a 'fresh' query for each time I created a new task. Isn't that true?

any help would be appreciated.

thanks in advance,

sincerely,

J__

[Brian C]

Im not sure if this is relevant or if this is in any way part of your problem, but I think it is worth mentioning for people that are new to putting their solutions into server enviornments.

In v6 and v5 (and quite possibly v7), global fields will save the last data that you used in them while in single user mode prior to placing the files onto a filemaker server. Once on the server that data will stay there as the default data for every user that accesses the files and will not change except under very unusual circumstances.

So in any case it is always good to make sure your globals are initialized before assuming their content.

I will take a harder look at your question when I'm more awake tomorrow if someone else has not already answered.

[J__]

Brian,

Thanks so much, I appreciate it.

I have tried using Refresh Window [Flush cache Joins] and it has gotten better, but I would appreciate some input from someone.

I look forward to your input

thanks,

Sincerely,

J__

[Brian C]

I am working on reconstructing this on my end, will post more when I have questions to clairify.

Quick note: contents of globals are not shared in a multi user enviornment. I dont think this is the issue but it is still worth mentioning.

[Ugo DI LUCA]

Hmm..

I don't know why you'd ever need a refresh step here, as nothing was modified to need a refresh.

You wouldn't need to pass the ProjectID in a global in my opinion, but this doesn't explain your situation.

Just take a look at this and see if this change when networked..

ProjectsTasks.zip

[Ugo DI LUCA]

You cans skip the first Commit Request as it is not necessary here.

Of course, make sure to fill Task Name and Status, or control the entries through the Create script

[J__]

Ugo,

thanks for the sample very cool.

Something I may not have made clear though is that the task name and status are on a different layout.

The Project view shows a specific project. At the bottom is a grid with all tasks currently associated with the project, which your sample correctly illustrates.

what i may not have made clear is that I click a button on the Project layout called "new task" and it takes me to a Task_New layout where I then enter task name, date and then pick from the status drop down. Would going to a different layout make a difference? I would think not, but ...

I originally wrote the create_Status number generatation code in a script because (1) I'm not as nimble at Relations as you are(compliment) and (2) my background is writing code to get it to work. I'm relatively new to getting relations to do so much of the work.

The thing was in my script what would happen is that If I am in Task_New and I have FM's validation on, I allow 'reverting' out of the record - who knows maybe they pressed the wrong button right? ; but then I might also click on a button on my Task_New layout which says "Go back to Project View" ; and then validation occurs (Revert / OK), if I click Revert, then I get "Script has been cancelled" with options "continue" or "cancel" ; if I click continue it executes my Status_Number_Create code and i get duplicate numbers... and I would also get a 2 as my first number rather than a 1. I can't skip numbers. I wasn't able to figure out how it was creating a 2 instead of 1, when there was only one task record. Any ideas?

would your calculation approach solve that problem?

Does a calulation always force the relation to be refreshed? like I said I would get 2 for my first status record sometimes and there was no one else was adding a record, so I figured that the refreshing was off or something, when I added it, it seemed to fix it.. seemed to.

any additional comments, tips, tricks or suggestions would really be appreciated.

thanks so much,

Sincererly,

J__