I have a table named Sales with the following data:
Based on Sample data below, i am trying to query the table by creating a relationship to find the ID that achieved a specified Unit amount on the earliest date.
In other words, and for example, which ID achieved a specified Unit amount first (earliest date)
Specified Unit Amt of 5 = 1 Expected records returned result
301 10/13/2018 6.5 Specified Unit Amt of 2 = 2 Expected records returned result
766 10/03/2018 2 360 10/03/2018 2
I think a selfjoin may be required. Any thoughts appreciated!
ID DATE UNITS
301 10/15/2018 9 301 10/14/2018 8.5 301 10/13/2018 6.5 360 10/14/2018 5.5 305 10/15/2018 5.5 301 10/12/2018 4.5 656 10/13/2018 4.5 360 10/13/2018 4.5 305 10/14/2018 4.5 611 10/14/2018 4.5 305 10/13/2018 4 15 10/13/2018 4 301 10/11/2018 3.5 360 10/12/2018 3.5 611 10/13/2018 3.5 301 10/09/2018 3 15 10/11/2018 3 656 10/12/2018 3 611 10/12/2018 3 200 10/14/2018 3 301 10/06/2018 2.5 611 10/09/2018 2.5 200 10/13/2018 2.5 766 10/03/2018 2 360 10/03/2018 2 785 10/05/2018 2 301 10/05/2018 2 611 10/05/2018 2 439 10/10/2018 2 656 10/11/2018 2 510 10/15/2018 2 360 10/01/2018 1.5 510 10/04/2018 1.5 611 10/04/2018 1.5 305 10/05/2018 1.5 200 10/08/2018 1.5 656 10/09/2018 1.5 785 10/02/2018 1 766 10/02/2018 1 305 10/03/2018 1 439 10/03/2018 1 15 10/04/2018 1 301 10/04/2018 1 200 10/07/2018 1 656 10/08/2018 1 766 10/01/2018 0.5 510 10/01/2018 0.5 611 10/01/2018 0.5 200 10/06/2018 0.5
By Richard Carlton
Summer is over and our weekly FileMaker webinars are back!
Our first one is this Thursday at 11am PDT, on “Relationships for Beginners”.
We will spend 30 minutes covering a specific topic. Then the last 30 minutes will be an open Q&A on any topic.
Sept 13th: Relationships for Beginners
Sept 20th: Find Records, Date Ranges, and Special Operators
Sept 27th: Conditional Formatting (Can I making something Red?)
Oct 4th: Automation (Repetitive Tasks can become Scripts)
Oct 11th: Reporting & SubSummaries
Oct 18th: Merge Fields and Hiding Objects (Harry Potter Invisibility Cloak)
Oct 25th: Sharing your FileMaker App with Co-Workers
Nov 1st: Basic Concepts for Building Mobile Apps
All the best,
CEO & Video Trainer
Just dipping my toes into FM and finding it opens many possibilities. Still must learn a lot to be proficient, so TIA for help.
Background: I sign out molecular diagnostic cases.
We have a compendium of tests run in the laboratory (all the tests that can be ordered, run and reported out) A Test (type) can be run on many patients. A test (instance) has one patient and each test/report/result has a patient (one). Each patient can have >1 tests. There are a set number of reporting options per test (TestA results can be Positive, Negative, Suspicious, etc (options are in a value list) Each report (ex: TestA/ReportPositiveHomozygous) has a specific text content (Table TestResults) (Pos/Neg, Report text, disclaimers, method, references) I seem to have figured out many of these relationships (I've attached my db)
Where I'm stuck:
1) I have not been able to create the relationship between the test result (tr_TestResult) and the patient instance to link the result text to the patientTest instance (currently TOC Patient|TestResult).
2) (not relationship related... rather a calculation problem): How to do the calculation so that TestOrdered:TestFinalized gets timestamped when TestOrdered:TestStatus is set to "Finalized".
Thanks for the help!
I'm sure there is a simple solution to this problem, but I can't seem to figure it out!
I have a custom app that allows the user to write letters amongst other things.
Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this.
Does anyone have any suggestions?
Thanks in advance.
Updated script triggering plug-in for FileMaker Pro 16 improves stability.
Alphen aan den Rijn, the Netherlands, December 11, 2017–Troi today announced the immediate availability of Troi Activator Plug-in 4.5.1 for FileMaker Pro 16.
What is Troi Activator Plug-in?
Troi Activator Plug-in is a very powerful tool for triggering scripts remotely. All from within FileMaker you can:
- trigger a FileMaker script on a different computer (remotely)
- trigger a FileMaker script over internet
- use the built-in security to prevent unauthorized triggering
- send a number (e.g. a record ID) and a long text with the trigger
- start and stop listening for remote trigger messages with one script step
- communicate with databases in different FileMaker versions or even runtimes
Except from remote triggering you can also use the Activator Plug-in for:
- triggering a script on a specified date and time
- scheduling events which trigger any script you want
- running a script from any FileMaker calculation, for example a tooltip calculation
- validating fields on exiting a record with a custom script suited to your specific needs
What is new in version 4.5.1?
Troi Activator Plug-in 4.5.1 is a maintenance release and has the following fixes and improvements:
- (Mac OS) Fixed a crashing problem with installing or disabling the plug-in: FileMaker Pro no longer crashes when disabling the plug-in. Note: if you have installed version 4.5 you need to remove the plug-in manually to avoid the crashing. You will find instructions how to manually update Troi Activator Plug-in 4.5 here: https://www.troi.com/additional/manually-update-troi-activator-plugin-instructions/
- (Mac OS) Fixed a compilation problem with code signing and extended attributes in Mac OS 10.12 (Sierra).
- Improved type-ahead of external function names.
Pricing & Availability
A fully functional demo version of Troi Activator Plug-in 4.5.1 is available for downloading at https://www.troi.com/products/activatorplugin/
Licenses cost US$ 89 per user. Details on developer licenses and multi-user discounts can be found on our web site. You can order licenses from our web site.
The 4.5.1 version works with the 4.5 version of the registration. If you bought a license for a pre-4.5 version on or after June 14, 2016 the upgrade is FREE! Eligible users have been sent a new registration string in September 2017.
Upgrades from licenses bought before June 14, 2016 are available from US$ 39 per user. Upgrade prices for other licenses can be found on our web site.You can order upgrades from our web site.
End of year sale: all licenses 10% off!
Until the end of 2017 we offer a 10% discount on all Troi plug-in licenses! Order your new licenses and upgrades for any of the Troi plug-ins from https://www.troi.com/shop/ and enter the following coupon code upon checkout:
The coupon code is valid until December 31, 2017.