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

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 flutegirl
      To try to explain the setup as succinctly as possible:
      Database is running on a FM17 Server (on a Windows virtual(?) machine... Our IT department handles that part).
      The FM database does a number of imports every morning from our primary (4D) database, using an ODBC driver, but I have not found a way to have those imports run successfully from the server, so I have them running on an instance on a secondary user account on my desktop computer.
      The scripts are triggered by adding a record to a table, which is checked every five minutes using an OnTimer script.  I run a separate instance of FM17 on my user account so that I can use the database even if there is an import running.
      This solution has been working (fairly) well for a number of years, but last week, my desktop computer was updated to Mojave (10.14.6), and since that upgrade, it seems that the imports on the secondary account will only run while that user account is active.
      1) Is anyone else doing anything similar, where they have an instance of FM running on a separate user account?
      2) If so, have you had any trouble with Mojave, or have suggestions on what may be going on?
      3) Any suggestions on how to fix this?  My initial thought is to just open the database using FMP on the Server system and try having the imports run from there, but am concerned that might also have problems.
      Any suggestions would be greatly appreciated.
       
    • By OneStop
      I have a field called Price. I need to create another field called calculated price that will add a percentage depending on the value of the original Price field.
       
      Example:
      If the Price is $100.00 or more Subtract 10% from the price
      If the price is $10.00 or less Subtract .25%
      If the Price is between $10 and $50.00 Subtract .5%
      If the Price is between $50 and %100.00 Subtract .75%
       
      and so on and so on...
      So if I have a record with a Price of $1.00 the calculation will fill in the Calculated Price field with $.75
    • By BAleiHi
      I have a layout calculation that looks like:
      Round(If($$NoGPABump = 1;Classes::GPANoBump; Classes::GPABumped);2)
      In most instances, this rounds the appropriate value to 2 decimals  and displays those two digits.  But on occasion, it displays only one decimal as in this one:
      .  Instead I want to see 4.20.  If I round to, say, 4 decimals, it works properly: .  Rounding to 3 digits gives 4.199.  How do I get the trailing zero to show up?  This seems to be consistent for all data that rounds to a last digit of zero.  Is this a bug?  Is there a workaround?  Thanks,
       
    • By vinnygi
      I have a layout with a number of button bars, all with the same style. Each button is set to have an inner shadow and a white fill on Hover. For any of the buttons that call a popover, after the popover is closed, the hover shadow no longer works, but the fill still changes to white.  I've tried adding a refresh object script trigger when closing the popover, but that doesn't work.
      Some of the buttons are currently placeholders, which just call a script that exits itself so I can test the hover state.  Those work fine.
    • By Will_Logic
      Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster?
      I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster?
      Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question).
       
      Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)] Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )] Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000] MBS command I think would be something like:
      MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. ) We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster.
      Tx any suggestions!
×
×
  • Create New...

Important Information

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