Jump to content
Sign in to follow this  
DreadDamsel

fmp17 Finding records between dates and according to a criteria selected in a dropdown

Recommended Posts

Hi.
I have a database that lists grant applications.
I need for my user to be able to view the records in a number of ways according to certain criteria for analysis purposes. These records are displayed in a List layout.

I need to be able to:

1) see only records where the submission dates are between x and y

then

2)  filter these records according to what is selected in  2 dropdowns - Outcome and Status. The Outcome field dropdown value list is : <blank>, granted, unsuccessful, unknown. The Status field is  dropdown value list is: <blank>,submitted, in progress, finalised. The <blank> is where that dropdown criteria are not required.

Ideally, I'd like to be able to set the dates, select from the dropdowns and perform the find, but happy to do it in 2 steps if necessary.

I've tried several things from solutions found searching the internet, but nothing seems to work and I'm getting increasingly frustrated with it. I'm not a coder and anyone good enough to help me out here needs to be aware of this. While I've been using FM on and off for a number of years, I'm by no means an expert or even near it.

Thank  you in advance for any guidance with this. You guys have helped me out in the past.

Share this post


Link to post
Share on other sites

I suggest you start by defining 4 global fields to hold your criteria -  gFromDate, gToDate, gOutcome and gStatus - and place them in the header part of your layout, next to a Find button. Make the button run a script that does:

Enter Find Mode []
Set Field [ YourTable::SubmissionDate ; "≥" & YourTable::gFromDate ]
Set Field [ YourTable::SubmissionDate ; "≤" & YourTable::gToDate ]
Set Field [ YourTable::Outcome ; YourTable::Outcome ]
Set Field [ YourTable::Status ; YourTable::gStatus ]
Perform Find []

That's a rough sketch, there are many variations and embellishments possible.

Share this post


Link to post
Share on other sites
2 hours ago, comment said:

 



Set Field [ YourTable::SubmissionDate ; "≥" & YourTable::gFromDate ]
Set Field [ YourTable::SubmissionDate ; "≤" & YourTable::gToDate ]

 

That wouldn't work. The second set field would overwrite the first set field.

Set Field [ table::SubmissionDate; Table::gFromDate & "..." & Table::gToDate ]

would work.

 

Share this post


Link to post
Share on other sites
3 hours ago, OlgerDiekstra said:

Set Field [ table::SubmissionDate; Table::gFromDate & "..." & Table::gToDate ]

Oops, I don't know where my head was. Thanks for the catch.

Share this post


Link to post
Share on other sites

Hi. Thank you for the help so far :)

I already had the globals.

I started to create the script but when I added the following into the Calculated Result for Set Field, I got the message saying an operator was expected:

Grants::Date Submitted;Grants::gSubmittedFromDate &"..."& Grants::gSubmittedToDate

Any thoughts?

 

Share this post


Link to post
Share on other sites

You must be doing something wrong. It works just fine for me:

image.png.947507b7254bfcbd76dc225613f7e331.png
Maybe post a screen shot of what you're trying to do.

Share this post


Link to post
Share on other sites

Still get the same error - just trying to get the dates thing worked out .

Screenshot attached.548175727_ScreenShot2019-02-28at4_21_28pm.thumb.png.0102c4e787663e526d8e6c667038dcb6.png

Share this post


Link to post
Share on other sites

The Set Field step has two parts.

Grants::Date Submitted is the field you want to set; you select it when specifying the target field.

When specifying the calculated result, enter ONLY this into the calculation window:

Grants::gSubmittedFromDate &"..."& Grants::gSubmittedToDate

 

Share this post


Link to post
Share on other sites
Posted (edited)

Thank you so very much for your help - as soon as you explained it I realised how obvious it was. I must have been so far down the rabbit hole that I could no longer see the obvious.

I was just going through the file in a final check and realised on my global summary layout, I still have an issue with a date thing.
I want to show the date of the first record entered and a date of the last record entered. I've done a Summary field for Min and one for Max but nothing shows up in the live view.

Any thoughts?

Screen Shot 2019-03-02 at 11.45.56 am.png

Screen Shot 2019-03-02 at 11.42.29 am.png

Edited by DreadDamsel

Share this post


Link to post
Share on other sites

Why not simply use gSubmittedFromDate and gSubmittedToDate? That is  the date range they've entered to search, if you showed them different dates, they might get confused, and think the system is doing something odd.

Share this post


Link to post
Share on other sites

This is a different purpose - it's simply displayed information, not entered. It's the date the first record is created and the most recent date a record has been created - this date is auto-filled on creation of a new record.

It displays on a different layout.

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.
Note: Your post will require moderator approval before it will be visible.

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.

Sign in to follow this  

  • Similar Content

    • By ChangeAgent
      Is this at all possible?
       
      I want to have two buttons that will each do the following. 
      Have one button to ‘Strike Thru’ text that is highlighted before.  Means not all the text in the field will be changed.
      And one button to change the text colour to Red, again highlighted before.  Means not all the text in the field will be changed.
       
      Any tips how to do this?  Please bare in mind I am a FMP beginner.  Thanks. 
    • By lutfishah
      Dear All,
      I am trying to put a conditional formatting validation which turns Red when the value is entered wrong. Basically all my tries have failed so I would like your help here.
      The criteria of entering a value is 
      xxxx-xxxx-xxxx-xxxx
      1111-2222-3333-4444
      1. Length of value is  = 19     OR      this should be a 16 digit number, in groups of 4 separated with dashes
      2. Non-Digits characters are not valid.
      Please could you help me with this and I will really appreciate. 
      Many thanks
      Regards
      Lutfi
       
    • By Koonce
      I have filemaker server on virtual server, I have a script that exports emails and or names i cannot get the file to be saved to the local desktop it tries to save to server with an error when i do manually it works fine so i am doing something wrong...
      Thanks in advance
    • By ThomaStudios
      I have been using FM17Adv for some time.  I am on a MacPro 12-core tower and I had been running macOS Sierra.  All the internal scripts I had in one database worked perfectly.
      I recently updated to High Sierra and now I am getting script errors I don't understand.  And this is from the same exact file I used previously.  I have one script where I want it to open a folder in Dropbox so I can access files there.  But I am getting this error:  Can’t make "Dropbox" into type integer.  Then I get a -1700 error dialog immediately after that.  Can anyone explain how I can fix this.  I have not been able to find anything online.
      The script is a native AppleScript, and I suspect that has something to do with this.  Here is the script:
      tell application "Finder"     activate     open folder " Pieces 2011" of folder "Teaching Material" of folder "Dropbox" of "  ThomaStudios 4TB"     select Finder window 1 set position of Finder window 1 to {2877, 167} end tell
      Thanks.
      J D Thomas ThomaStudios
       
    • By Caio Euzébio
      Hi Guys,
      There are any logic or resource that i can use to make like a "text to columns".
      Make text from field one flow to another fields but only with in the middle of coma regarding your sequence, like a excel?
      Full text with Comas Text1 Separated Text2 Separated Text3 Separated How,Are,You? How Are  You? Could please help me, i need a lot of this resource.
       
       
×
×
  • Create New...

Important Information

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