Finding status data

[Bruno Dini]

Hello, I'm developing a solution to organize my office, but I'm having a little problem with one script, that I believe is very simple to accomplish.

I have two tables Projects and Jobs that are related with each other. I can create many jobs that are related to a specific project.

Project and jobs have a status field, that can be Open or Closed.

My script is to set the status closed to the Project, but for that I must be sure that aren't any open jobs related. The script works OK when there's an open job there, it displays a Custom message saying that are some jobs open and they must be closed before I close the project. The problem is when all jobs are closed, it is supposed to set the current date on a field within the project, but all it displays is a error message saying that there are no records that match that request.

The script is down there, I hope somebody can help me with this one, or with another solution to do the same thing.

Go to Related Record [show only related records; From table: "Jobs" Using layout: "Jobs" (Jobs)]

Constrain Found Set [Restore] (here the find is all the open jobs)

If [Get (FoundCount) >= 1]

Go to Layout ["Projects" (Projects)]

Show Custom Dialog [.....]

Else if [Get (FoundCount = 0]

Perform script close project

End if

I believe the problem is because the found set is 0, but I don't know what to do to make this work ok. Any ideas?

Sorry for bad english.

Thanks in advance

[bcooney]

From a Project record, you can tell if there are any open jobs by building a relationship "OpenJobs" from Project to Jobs by ProjectID and Status=Open (create a calc field in Projects that equals the word "Open").

If not isempty( OpenJobs ), then you have an Open Job.

So the script would be:

//Starting in Projects

If (not isempty (OpenJobs))

//close Project

Set Date_Closed to get (currentdate)

Else

Show Dialog "Sorry, you have open Jobs."

EndIf

[comment]

You can find out if any related jobs are open, without adding another relationship. For example (assuming a job is closed by filling a DateClosed field):

If [ Count ( Jobs::ProjectID = Count ( Jobs::DateClosed )

Set Field [ Projects::DateClosed ; Get (CurrentDate ) ]

Else

Show Custom Dialog [ "Sorry, you have open Jobs." ]

End If

[bcooney]

Although I like your approach, he said that he has a text field "Open" or "Closed". Therefore, I don't think the count will work.

I would have a date_closed field, and possibly a flag which =1 if date_closed. I like being explicit with flags.

[comment]

he said that he has a text field "Open" or "Closed". Therefore, I don't think the count will work.

Oh, a count will still work - just not the same count as the one in my example. I deliberately chose to hint of a more straightforward method of handling the jobs status. But if one insists, one could look for:

ValueCount ( FilterValues ( "Open" ; List ( Jobs::Status ) ) )

or just:

IsEmpty ( FilterValues ( "Open" ; List ( Jobs::Status ) ) )

But here the literal "Open" must be hard-coded into the formula - so if the bosses decide they like "Active" better, there will a LOT of changes to make.

BTW, if the job status happens to be unstored (say if it depends on the status of job's tasks), then building a relationship upon it will be problematic - while the count will still work.

and possibly a flag which =1 if date_closed. I like being explicit with flags

What would be achieved by having such flag, that couldn't be achieved without it?

[LaRetta]

If [ Count ( Jobs::ProjectID = Count ( Jobs::DateClosed )

Now I like this ... if the total related count (based upon ProjectID which is never empty) is same count as the field which determins the criteria (hopefully DateClosed) then ALL related are closed. ANY field can be used for this 'all or nothing' type of comparison!

Nice example, Michael. If it were me, I'd use the DateClosed and simply display (using layout number formatting) as boolean with only the word 'closed' in it. Using a date, such as DateClosed will eliminate the possibility that the boss will change their mind on the wording of Open to Active, which would save a lot of data change, as you've said.

Edited December 20, 2008 by Guest
Added and then changed sentence

[Bruno Dini]

Hello guys,

Thanks for the answers, they all were very helpful, and help me understand some features here.

I tried both ways and all worked very fine, but I'll be working with comment's way, wich I think is simpler.

Thanks a lot.. :-D

[bcooney]

Comment, reading this again, I've come to really like the calc method. It's always better to avoid hard-coding (in value lists, in a relationship, in scripts and in calcs). You won me over with the problem of an unstored calc in the bottom half of the relationship.

Thanks for the nudge out of my "usual" way.