Calculate number of times specific field says "Quote"

[Mike513jr]

Hello everybody, I am trying to display some calculated fields on a dashboard that will get information from related tables.  I am having some difficulty in trying to get this to work properly.  Any help will be greatly appreciated.

 

I have attached an image of my table layout.

 

I have a field in one of my tables, project_db, that is called project_status.

I have the following options for this field with the use of a value list:

 

Quote

Awarded-Won

Awarded-Lost

Verbal

Order

Invoice

 

I would like to display, on my dashboard layout, the number of records that have "Quote", "Verbal", "Order", "Invoice, etc., selected.

 

Is there a way to do this?  I have looked around the forums, but have not had any luck in finding a solution that works for what I am trying to do.

 

Thanks in advance for any help.

[OlgerDiekstra]

It's not very dynamic, but if your value list doesn't change that may not be a problem. One way to do it is to create boolean calc fields (ie IsInvoice, IsQuote, IsOrder, etc) for each value which will only return 1 (true): if ( Self = "Invoice"; 1; 0).

You can then use a summary field (sumTotalInvoices, sumTotalOrders, etc) to sum all the 1's for the IsInvoice fields, etc.

These fields would go in the project_db. You can then reference those fields from the dashboard.

[eos]

A more structured approach: 

 

• instead of a value list, set up and use a Status table

• create a Cartesian relationship Dashboard x Status

• add a portal into this relationship to the dashboard layout

• add a relationship Status::status = Projects_summary::status (or better: Status::id = Projects_summary::id_status)

• add a field from Projects_summary to the portal (summary sCountOf or unstored calculation cFoundCount) 

[Mike513jr]

OlgerDiekstra - thank you for the reply, I have tried what you suggested, and the resulting field displays no information on the dashboard.  I must be doing something wrong.

 

I have attached an image showing the additions I made to the project_db.

 

I am using the sumTotalQuotes as a field on the dashboard - this field won't display anything for me.

 

What did I miss?

[eos]

See the attached file.

Dash_eos.fmp12.zip

[Mike513jr]

Attached is an updated file - this one does a good job of showing an "almost complete" solution - with some good detail.

 

Thank You in advance for your help.

 

-Mike

iPad - 1.25.15.1.zip

[eos]

See the modifications; note the script trigger that injects the accountID into the Status table. Obviously, this only works when viewing one account at a time, but for a List view, you should/would use a Projects-based layout with sub-summary parts anyway. 

 

Speaking of IDs: I strongly suggest that you create an auto-enter, serial, meaningless IDs as primary key, instead of the account name.

 

Also adapt a nomenclature to the effect that

Account::id (parent table's primary key) = Projects::id_account (foreign key in child table)

Your nomenclature of Account::id_account = Projects::id_project runs contrary to established practices, and is quite confusing. 

 

Last but not least, give the Projects table its own primary key, too (e.g. Projects::id), following the same guidelines.

 

If you proceed to employ a Status table, also give it a primary key (I left this out of the modifications) and use that one for relationships; as a matter of fact, every table you create should have a primary key as described above.

 

Note that a different approach wold be to use ExecuteSQL() to collect a list of status types and their summarized values for an account.

iPad - 1.23.15.4_eosMOD.fmp12.zip

[OlgerDiekstra]

OlgerDiekstra - thank you for the reply, I have tried what you suggested, and the resulting field displays no information on the dashboard.  I must be doing something wrong.

 

I have attached an image showing the additions I made to the project_db.

 

I am using the sumTotalQuotes as a field on the dashboard - this field won't display anything for me.

 

What did I miss?

You don't want to count in the summary field (which will just count the number records), you need to total all the one's together.

Create a table view and see what values the records have. For each record that has "quote" you should have a 1 in the IsQuote field. The sum field should have the total of quotes.

[Mike513jr]

OlgerDiekstra - 

 

I must still be doing something wrong.  I have done exactly as you have told me to - still no luck.

 

I have attached 3 screen shots showing what I have done - where am I still getting this wrong?

 

Thanks.

[Mike513jr]

I finally made it work - by changing the table that the dashboard read from - I point the dashboard layout to the project_db table.

 

That is working now.

 

If I may ask another question - is there a way to total all of the projects that have the status of "Invoice" or "Quote"?  I would like to display the total dollar value of all Quotes, Invoices, etc.

 

I have tried this, and it shows the value for ALL the projects.

 

Thanks again for all your help.

[bruceR]

Time to learn the basic concepts of subsummary reports.

At some point your dataset may become large and you will want script the capture of this data.

[Mike513jr]

I figured it out - I have the totals working for the different statuses.

 

BruceR - you are correct - I may outgrow this solution and have to learn to implement scripts to get the job done.

[OlgerDiekstra]

You can apply the same technique; create a calc field: if ( Self = "Invoice" or Self = "Quote"; project_totalprice; 0)

Then create a summary field that totals the calc field.