Jump to content

Calculate number of times specific field says "Quote"


This topic is 2872 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

post-81371-0-81663500-1422152369_thumb.j

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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) 

Link to comment
Share on other sites

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?

post-81371-0-37420400-1422191546_thumb.j

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2872 days old. Please don't post here. Open a new topic instead.

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
 Share

×
×
  • Create New...

Important Information

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