Where do I begin? re:Count some records but not others?

[Lee Brock]

Hey All,

I'm reposting this in what I think is now the proper forum.

Thanks in advance for your help.

I've modified the Business Productivity Kit. Everything has been going smoothly, but it has become apparent that I need some more foundational information. The User Guide gives very little information on Functions, Operator and what any of those pesky little symbols mean. I found the filemaker.com pdf file on functions, but putting it all together is another thing, eh? Anybody know where I could find a quick low cost source for info on the symbols and the rest? I guess that stuff is too basic for this site or maybe I'm just not searching in the right place. I have a book on the way, but I can barely wait for it to arrive. : And I'm amazed at how much I've already learned just from searching and reading this site. Thanks to all how contribute.

Here's my problem:

In the File, bps_Sales_Orders.fp7/Form View I've set up a field (Invoice Type) using a value list containing "Invoice", "Memo" so that I can designate a record either an invoice or a memo. In my summary line of the list or report I need to count the invoices, but not the memos.

I got a calculation suggestion from another user: c_count_lines = Case(Invoice Type = "Invoice"; count(invoiceline_id)) but I can't seem to make it work. I think I understand the Case function, but I get error messages for the first and last parts "Specified field cannot be found."

Thanks you very much.

[aldipalo]

Did you choose the field from the calc screen or type it in? You may have stipulated the wrong table. I.E. Contacts instead of Invoice.

Just a quick thought. When it tells you it is not finding the field that's the first thing I check.

HTH

[Lee Brock]

Thanks aldipalo

These are the settings:

Define database for “bps_Sales_Orders”

Evaluate this calculation from the context of: Sales Orders

Current Table (“Sales Orders”)

Invoice Count =

c_count_lines = Case(Invoice Type = "Invoice"; count(invoiceline_id))

Invoice Count is the name of the field I’m defining.

I selected Invoice Type from the list of fields window. Invoice Type is the name of the filed I created that holds the Value list:

Invoice

Memos

Calculation result is Number

Number of Repititions is 1

The checkbox for Do not evaluate if all referenced files are empty is selected

I get the error message: specified field can not be found, for both

c_count_lines

invoiceline_id

I just don’t know enough to understand what those two pieces are referring to.

[comment]

It would be far better to take up the issue in the original thread. As it is, no one can understand what's going on here without reading the other one.

I believe the advice you were given there assumed your calc field would be named c_count_lines, and it would hold this formula:

Case(Invoice Type = "Invoice"; count(invoiceline_id))

It also assumes there is a field named invoiceline_id.

---

I should add that I have no idea what the calc is supposed to do, and I suspect it will not do what you expect it to do. Such are the vagaries of double-posting...

Edited September 13, 2006 by Guest

[Lee Brock]

I see. Thanks for letting me know.

There wasn't much to the original thread. I pretty much copied over all the info into this thread, hoping it would be clear. Guess not, my bad. I don't know how to put any more clearly.

That said, I still get the error for

count(invoiceline_id)

I haven't created a field with that name and I don't know what it means. It is supposed to count the result of the case function?

Maybe I should just drop it until I can study up a little.

Thanks for your help.

[aldipalo]

Hi Lee:

That said, I still get the error for

count(invoiceline_id)

I haven't created a field with that name and I don't know what it means. It is supposed to count the result of the case function?

To reiterate what Comment said If you haven't created a [color:red]invoiceline_ID field you will get an error "Specified field cannot be found." In the example you gave your table should have 3 fields named:

c_count_lines

Invoice Type

Invoiceline_id

If any of those fields do not exist the calc will fail. You must either create those fields or reassign those field names to current fields within your table.

Usually, when someone gives you a calculation they are adding bogus fields, unless you have provided a list, and you must change the calculation to the corresponding fields from your table.

HTH

Al

[Lee Brock]

Thanks Al. I created a field named invioiceline_id. I made it a number field, for the time being. That stopped the error message. Now I just need to understand what that field is for, so I can define it properly.

I put the field c_calc_lines in my layout, but the result is blank.

I was wondering if maybe c_calc_lines is supposed to be a summary filed of invoice_line_id???

[Lee Brock]

Ok folks,

Thanks for your help so far. I got my book. I'll work on it and come back with a better general understanding, and take it from there.

[Lee Brock]

I got it. Thanks for everyone's help.

Since I made the mistake of posting this topic in two forums, "Define Fields", and "Custom Functions and Help"

I'm posting my result in both. Hope that's ok, just this once. :

Here's what I ended up with:

Invoice_Validation =

Case ( Type = "invoice" ; Count ( Invoice ID ))

This calc looked at my field called "Type" and found each case where the data entered was "invoice". Then it counted the specified record field, which in this case was a field called "Invoice ID."

After doing some reading I learned that this calc was only going to create a count for each individual record, and so I got a result of 1.

From there it was easy to figure out that I needed a summary field calc to do the rest..

Summary_Invoice_Validation =

Count of Invoice_Validation

Maybe there's a way to combine these two steps?

Any ideas?

But for now it's working.

BTW, in previewing this post I see that a space is being added to the word Validation in the field name "Summary_Invoice_Validation" that really isn't there.

Thanks