Jump to content
Sign in to follow this  
Bruno Dini

Finding status data

Recommended Posts

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 by Guest
Added and then changed sentence

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.