Here is my situation.
I have a 25x25 grid - let's call it a map. Think of it like a game board, like Battleship or perhaps an Excel spreadsheet.
I have a 9x9 view of this grid since it is not possible for a phone user to practically view 25x25, but 9x9 seems to work fine.
And I have the concept of Current_Location on the map, which is dynamic and changes based on other factors.
In TABLE1 I have records of all the "cells" in the 25x25 map that hold information about the cell (for example, the color of the cell). There is other information stored here that includes, for example, whether you can "see" other cells from this particular location in the grid. So in the record for, say, cell B5, I have a field that houses a return-delimited list of the other cells that can be "seen" from this cell (B5 in this instance).
Another table, TABLE2 has records that are also have a field for Current_Location, and it tells me which cells (of the 25x25 grid) should be displayed in the 9x9 grid. Usually, the Current_Location is the middle cell in the 9x9 grid, but if the Current_Location is on the edge of the 25x25 grid, then the 9x9 view is adjusted accordingly and the Current_Location is shown on the edge of the 9x9 grid. The user can also change their orientation so they can view the map from a different direction. The way I store this information is in a field that simply lists (return-delimited) all the cell that should be shown in the 9x9 grid. So in our previous example of B5, the record for B5 has a field called Grid that is a list of the 81 cells that should be shown in the 9x9 grid.
For other reasons, I cannot combine these two tables even though they both are based on the concept of relating information to Current_Location. TABLE1 is actually not as simple as described, but I'm trying to limit the scope of my question.
So here is my question: I have 81 (sigh) objects on my layout for this 9x9 grid. I need to access information in TABLE1 for each of the cells that are represented in the 9x9 grid.
For example, let's again assume that my Current_Location is B5 and by using TABLE2 I know the names of all the cells shown in the 9x9 grid (they are stored in a list in a field in TABLE2). How do I then get information about each of those 81 cells out of TABLE1. I.e., each of the 81 cells should be colored according to information in TABLE1.
So, for the top-left cell in the 9x9 grid (which appears as the FIRST item in the list field in TABLE2), I need to be able to look up the color in TABLE1, and use it to drive Conditional Formatting of the cell. So if Color(B5)=3, then make the background color of the cell green. I can grab the cell name, and could easily write a script to figure out the color, but I need to *look up* the color to use it in Conditional Formatting.
I was trying to generate another field somehow that was an analogous list of 81 items that showed the color of each of the 81 cells (in the correct order), but 1) I couldn't figure out how to do that without running a script (which would take too long since these things are changing frequently and I really just need a data lookup); and 2) I think there must be a much cleverer way. I am still very new to join tables, but I think the answer is in that arena somewhere. The other idea I had was trying to write a Custom Function since I can easily grab the name of the cell, B5. But I don't know how to create such a function since it needs to access a particular table and it seems like functions don't really work that way.
Thanks in advance for any help!!
The new version now includes 24U Toolbox Plug-In version 3.0.3
Prague, CZ — January 31, 2019 — 24U Software has updated 24U FM Bench to version 1.3 which now includes code-signed 24U Toolbox Plug-In 3.0.3 with many new features and improvements.
What's new in version 1.3
24U Toolbox Plug-In 3.0.3 is now included with 24U FM Bench. This new version of the plug-in brings several new features and improvments, including compatibility with latest operating systems and the FileMaker plarform. The plug-in is now also code-signed. Code-signing is a security feature that allows user to verify the identity of the plug-in publisher and that the plugin has not changed. This makes it easier to deploy FM Bench in environments with strict security rules, where only code-signed software is allowerd to be installed.
About 24U Toolbox Plug-In
24U Toolbox Plug-In is a stable and reliable client & server-side plug-in providing a set of handy functions for your daily use, such as Execute SQL, running system scripts etc. The new version added support for iOS and FileMaker Cloud, and a great new function for converting FileMaker-native styled text to clean CSS-formatted HTML.
About 24U FM Bench
No more compromises! Gettting the best performance out of FileMaker Pro by optimizing user solutions and achievement 100+% speed gains in almost no time.
Detective benchmarks user solution. It measures how long everything takes and stores the measured values in a log for further analysis.
Bottleneck finder finds the bottleneck of user solution. It calculates the total time spend within each script over a specific time period and tells you which script consumed the largest part.
Session browser lets you examine individual user sessions. User can see every single action that was measured within each session and how long FileMaker spent executing it.
This great not for optimization but even better for troubleshooting. Can be seen what users were doing when.
Availability and Pricing of 24U FM Bench
Licenses for 24U FM Bench start at US$197 per seat and $1177 per server. Volume discounts are available for 5 or more seats.
More information: https://www.24usoftware.com/fmbench/
About 24U Software
With a team of full-time testers, web, plug-in, Mac, Windows, Linux, iOS, and Android native app developers, and FileMaker certified developers, led by the FileMaker DevCon 2015 Developer Cup champion, 24U excels in taking care of existing FileMaker solutions, optimizing their performance, identifying and resolving potential issues and liabilities, improving reliability, stability and scalability, integrating them with other systems including various hardware devices, and extending them beyond expectation.
As a platinum member of the FileMaker Business Alliance, we help global businesses around the world to maintain sustainable growth by working with their in-house developers or completely taking care of the maintenance and development of their business solutions.
Software Division Manager, 24U s.r.o.
(c)2019 24U s.r.o. All rights reserved. 24U, 24U Software, and FM Bench are trademarks of 24U s.r.o. FileMaker is a trademark of FileMaker, Inc., registered in the U.S. and other countries. All other trademarks are the property of their respective owners. Pricing and availability are subject to change without notice.
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!
Who Viewed the Topic