Jump to content

Welcome the ORIGINAL FileMaker Community

Take a moment to join us, no noise, all FileMaker...We Promise

GMak

How to select and count only specific entries from single field with multiple choices and give a total number of those records

Recommended Posts

GMak
Posted (edited)

I have an FMP16 database where I track volunteer projects at various stages of completion.  A critical field is "Project Status" and there is a drop down menu of 7 possible categories:

1. Awaiting Initial Review

2. Pending -No Decision- Need Info

3. Pending Evaluation /OK to Evaluate

4. Waiting Final Decision (Review Done)

5. Accepted

6. Declined

7. Project Completed

 

(I have numbered them in case any repetition of words give the search/find function trouble.)  I have successfully created a script that will return in a "Report Summary Page" various subsets of information about each account, for each status stage: e.g., for "Find 'Pending ' projects:

(SEE FIRST attached image)

 

where Find Mode is: (see SECOND attached image.)

 

So far so good.  (Although for "pending" it includes both #2 and #3, for the time being, this is workable.  Eventually I would like to be more specific)

 

Additionally, in the header of the "Summary Report" I have: ( SEE Third Attached Image)

 

 

Which returns the following in a report summary:  (In this case, it tells me in the header that I have 21 projects for 2019 as of 4/12/19 and this is Pending Project #16.) As below (redacted for confidentiality): \

(SEE FOURTH attached image)

 

 

THE PROBLEM/QUESTION:

HOWEVER, I need a find script to be able to choose and count MULTIPLE STATUS CATEGORIES IN A SINGLE FIELD but not ALL of them.  For example, choose "Project Status" #1, 2, 3, 4, 5 but not 6 (complete) or 7 (declined.)   

 

I've tried using Count, "...", listing "1." (then next line) "2." (then next line) "3." etc., and several other variations.  I thought I had a solution in "Edit Find" because you can enter multiple filters, but then I realized each filter has to be from a different field, so my "Find Year =2019" and "Find Project Status Pending" work, but not if I also want to also add AND FIND Project Status "Waiting Final Approval."

 

I hope I've provided enough (but not confusing) background info.

 

Would appreciate any suggestions!  Thanks!

Screen+Shot+2019-04-12+at+11.15.32+AM.png

Screen+Shot+2019-04-12+at+11.16.03+AM.png

Screen+Shot+2019-04-12+at+11.10.32+AM.png

Screen+Shot+2019-04-12+at+11.11.13+AM.png

Edited by GMak
Didn't have embedded images as reference and had to upload.

Share this post


Link to post
Share on other sites
Steve Martino

You could use a multi key relationship and have your project status as a checkbox set, attached to an OnObjectModify script trigger to update the relationship.  

Or you can script it by gathering the project status selections and use a loop script and extend found set to perform an 'and' find. 

Share this post


Link to post
Share on other sites
GMak

Sorry, but I can't comprehend anything of your explanation except I should to try to use a checkbox set.  However, a checkbox is just another form of the same thing rally, no?... I still need a script that will count only pre-specified checked box selections (same as drop-down selections)... in the Find Request script set up, you can only choose one parameter for each field.  Multiple parameters for each field would solve this.

I gather then, that FM is incapable of making simple calculations as in Excel, to (=Countif("pending") + countif("accepted")..." etc., hence the convoluted approach?

I also see you suggest a loop set.  I will try to develop this from previous DB scripts I may have.

Thanks!

Share this post


Link to post
Share on other sites
Steve Martino

I’ll post a sample file tomorrow. 

Share this post


Link to post
Share on other sites
GMak

Thanks!!! That would be very kind if you can.  I'm much better at Excel than FM and perhaps I'm stuck in "Excel thinking" for a solution.  I am experimenting with "constrain found set" to see if that works, but it seems to have the same limitations without a loop...

Share this post


Link to post
Share on other sites
GMak

I think I got it.  This works to return only the 4 categories and NOT "declined" or "project completed."  Somehow, I was able to add multiple FIND conditions under "constrain found set" which then appeared under "2 Enter Find mode" as well.  Not sure what I didn't do right previously....

1 Show all records
2 Enter Find Mode (Pause: off]
3 Set Field [Assignments::Project Status]
4 new records request
5 Constrain Found set [Restore]
   √Specify Find Request
      Find Records... Assignments::Project Status: [=“Accepted”]
     Find Records... Assignments::Project Status: [=“pending”]
      Find Records... Assignments::Project Status: [=“waiting”]
      Find Records... Assignments::Project Status: [=“awaiting”]
6 Go To Layout ["All Projects Report Summary" (Assignments) ; Animation:None]
7 Sort Records (Restore l with dialog: off]

So this is what I was looking for.  I don't know why I missed being able to add multiple finds.  I'm still not sure but it worked when I added them to Constrain Found Set.  Perhaps I didn't do them right under Find Mode.  In any event, It works as I'd hoped.


Thanks for your time to help - and I am still interested in learning your solution with loops if you have time.  I really don't know how to do that.


Thanks!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use