Field rule errors at set level

[Stephen Williams]

I want my customer table to be automatically maintained as "Active" or "Inactive", depending on the customer having an "Active" job. Customers can have unlimited jobs, and this does some on the fly file maintenance.

In Customer I have a field STATUS with the following field rule
IF(DBSUM("Job","CID",CID,"Status1")STATUS1 is 1 or 0
CUST->STATUS is "Inactive" if the sum of linked jobs is less than 1, otherwise it is "Active"

The idea is the customer file is maintained automatically.

I have a set JOB->CUST linked on CID (customer ID)

When I edit the set I get the message
Warning [1]: Error(s) opening rules for field: STATUS
Warning [2]: Error adding calculated rule to database engine.

What could this error be? If I delete the rule in Cusomer table error goes away, but what is wrong with the rule, though?

[Stan Mathews]

RE: Field rule errors at set level

Shouldn't this

IF(DBSUM("Job","CID",CID,"Status1")"CID")=0, "Active","Inactive") , "Active")

[Stephen Williams]

RE: Field rule errors at set level

No. The expression evaluates exactly as I want in the expression builder window. And I do not get error messages at the table level. Only at the set level.

The index CID is used by the set to make the link. I thought MAYBE the function could not access the indexed data at the same time as the set is open? So I went to create another index using CID but A5 in it's infinite wisdom simply dumped it as obsolete (it already has the "CID" indexed, why have two?)

And wow that was a fast response!

[Tom Cone Jr]

RE: Field rule errors at set level

Stephen,

How about another approach?

why not add a 'PendingJobsCount' field in the parent table? As each new job is entered use the post field rule in the child table to increment the PendingJobsCount field, as each is finished used the post field rule to decrement the PendingJobsCount field. You can always find your 'active' customers by filtering for PendingJobsCount greater than zero. An advantage of this approach is you're not doing table wide sums and counts each time a record is changed or entered in the linked child table.

-- tom

[JohnZaleski]

RE: Field rule errors at set level

If both of your fields are calculated fields, one in the customer table and one in the job table, then I don't think your logic works. Calculated fields are only re-evaluated when the record is changed. Changing the job record does nothing to the customer until you change the customer.
Tom's idea is a possibility. Another is this: for a customer record to be active, it doesn't matter how many jobs are active, one is enough. If you had an index on the job record that was
custstat: custID + if(status >1,"Y","N")
Then you could fire an event on the on save event of the job that would update the customer record:
if exist(custid + "Y","jobs","custstat") then
update the customer status to active
else
update the customer status to inactive
end if

[Stephen Williams]

RE: Field rule errors at set level

Thank you Tom and John both - I will try Tom's approach when I get to work tomorrow. I originally thought of using posting rules, but only thought of using 1 and 0. I think it ought to work fine.

The other thing is in my work jobs rule. Everything I do - estimates, scheduling, billing, invoicing, job costing is job based. But all the contact is with the customer. So the customer table is always a child table in my sets. And I always use forms based on these sets, so if I edit the jobs the customer data gets updated too. It was (and is, as far as I know) working... I am just bothered to see this warning message and know something ain't happy.