Page 1 of 1

Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 5:35 pm
by gkuhn
Would appreciate if I can get some assistance with the following.

Have the CLIENTS table, where I added new field "Current Items".

Have the INVMASTER table, where there is a field, named ITEM STATUS which has following condition - A / D / H / I and T. Every inventory items has one of these conditions assigned to it.

In the CLIENTS table, I would like to find the number of ITEM STATUS records not equal to T, for that particular client.

The Current Items field in CLIENTS has the following derivation:
Count of "INVMST" "Form Status" not = "T"

So for a client with four total records in INVMASTER, 1 record has status of T, 1 record has a status of H, and the other two records have a status of A, I would like the return value to be 3. Currently, I am returning value of "4".

Any one offer some assistance for this ?

George

Re: Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 7:45 pm
by Phil Winkler
Hi, George,

You have to add a field to InvMaster named Not_T (whatever) with a derivation of: if(FormStatus not=T,"X",blank)

On the CLients from add a field Not_T, text, 1, virtual with a derivation of "X".

Add the Not_T=Not_T in a new relationship between the two forms named rNot_T using the original match field(s) plus the Not_T fields.

In the derivation in Clients just use: count of rNot_T

How's that? Not_T is termed and abstract key. Les Cardwell wrote an excellent white paper on the topic for his Phd thesis. Want a copy?

Re: Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 8:08 pm
by gkuhn
Phil -

I would really like to read the paper - just helps me understand more of what I try to do.

I had a thought that I might need a named relationship, but then I was not sure what I actually needed to make this work. Will go back and focus on these new fields.

Thanks for you assistance . . .

George

Re: Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 8:40 pm
by gkuhn
Phil -

Followed your instructions and now I have the data I need - worked perfectly !!

Thank you my friend . . .

George

Re: Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 9:37 pm
by Phil Winkler
You can probably google: abstract normalization
and find it on the web.

Ensure you check that the Not_T field rederives if the status changes. That can be an issue.

Re: Using "Count of" capability in a form

PostPosted: Fri Jul 27, 2012 9:52 pm
by Fred Kingston

Re: Using "Count of" capability in a form

PostPosted: Sat Jul 28, 2012 7:08 pm
by gkuhn
Phil -

Just down loaded the PDF of Les's thesis, will read it as I have some time.

I had checked for updates when I added your code. It worked perfectly. I have since written two other abstract keys, one to show me the number of items that have been "discontinued - D" and still have OnHand Inventory > 0 and a second to show items that are ready to be dropped from the system. These items have been "discontinued - D" and no longer have any inventory and are now ready to be removed from the system.

So your assistance has been a big help for me. Much appreciated . . .

George

Re: Using "Count of" capability in a form

PostPosted: Sat Jul 28, 2012 10:37 pm
by Phil Winkler
The ANF technique is really, really usefule and fully relational according to Codd and Date.