Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Industry
    Real Estate
  • Location

FileMaker Experience

  • Skill Level
  • FM Application

Platform Environment

  • OS Platform
  • OS Version
    Win 10

FileMaker Partner

  • Certification
    Not Certified

V8S's Achievements


Rookie (2/14)

  • First Post
  • Conversation Starter
  • Week One Done
  • One Month Later
  • One Year In

Recent Badges



  1. I did not know that. Thanks for the heads-up. I will do some tests to figure out which is faster
  2. For now I've decided to do one ExecuteSQL that inserts SupplierIDs and an Age_Group into a table, and then do another ExecuteSQL on that to get a COUNT for each Supplier and Age_Group. But if anyone knows of a more elegant solution.... I forgot to say I'm use Monkeybread. A calculation field will always be affecting performance in the table whether I'm using the data or not. Every day the calculation will change as the age grows. The system I am building will be run by the server overnight, so a huge set of KPIs are available the next day. Having the server take 10 seconds to do this function at night is far superior to having the user and server affected during the day.
  3. I am purposefully avoiding calculation fields because our old database ended up filled with them over the years, bringing it to its knees. The data set, while not huge, is significant enough that a calculation that depends at run-time on the current date to calculate its age would be highly inefficient. I believe Filemaker would need to update all of those calculations every time before the SQL could do a SELECT. I may be wrong. I can't even SELECT into another temporary table and from there count by SupplierID, because at the very heart of the problem is being able to GROUP BY the CASE of Age. Very frustrating and so easy to do in proper SQL.
  4. I have Product records that have two fields: SupplierID number field Last_Updated date field I want to group this data by the Supplier and a set of Age categories depending on how long ago the Product was last updated. This will allow me to chart each supplier to see where data needs updating. For the non-working example, I'll only include two age categories for clarity. SELECT SupplierID, CASE WHEN Last_Update < ? THEN 1 WHEN Last_Update > ? AND Last_Update < ? THEN 2 ELSE 0 END As Age, Count(*) FROM Products WHERE SupplierID IN (SELECT DISTINCT SupplierID FROM Suppliers) GROUP BY SupplierID,Age"; ""; ""; Get(CurrentDate)-7); Get(CurrentDate)-14; Get(CurrentDate)-7 ) As far as I can tell (and I've experimented a lot with different workarounds) it is NOT actually possible to achieve this in Filemaker SQL in one go. In full fat SQL you cannot GROUP by a column alias as I've shown above, and obviously neither can Filemaker. A sub query in the SELECT would be one way of doing it, but in Filemaker you can only subquery in WHERE. Filemaker does not allow Common Table Expressions (CTE), which would be ideal. Using CASE within GROUP BY doesn't work, e.g GROUP BY SupplierID,CASE WHEN Last_Updated < ? THEN 1 ELSE 7 END Can anyone think of any crafty workarounds that don't involve looping through records and doing many SQL calls?
  5. A small number of international remote workers require access to specific customer records that they should work on. Each worker has a code number and customer records are tagged with their code number accordingly. For years I have used a complex system of restricted privileges and scripting to hide the thousands of other records from these users. User experience is VERY slow. As suggested on one of the Filemaker forums, I am trying to implement a new system using a CONTROL table of customer IDs linked via a relationship to the CUSTOMERS table. CONTROL TABLE CustomerID Control_Code 123 UserA CUSTOMERS TABLE CustomerID Control_Code Name Address 123 UserA Mr Joe Bloggs 456 Main Street A layout based on the CONTROL table shows fields from CUSTOMERS. The user can navigate their customer records without the performance overhead of loading thousands of customer records they shouldn't access. This is much faster. When UserA logs in, a global list variable sets which code(s) they can access. User $$AccessibleControlCodes UserA X Y UserB Z UserA should see only records in the CONTROL table records with a Control_Code of either "X" or "Y", but not "Z". A privilege restriction on CONTROL::Control_Code works. If I show all records, UserA can see X and Y records. Z records show "No Access". not IsEmpty(FilterValues ( Control_Code ; $$AccessibleControlCodes )) It would be annoying for UserA to have to flip through dozens of "No Access" records, so I still need to limit the visible records with a Find. Sadly, Find does not work at all on the layout. Doing any Find on any field always returns no results. Even though I am logged in as UserA and can see the X and Y records, trying to do a find of those same records doesn't work. I imagine this is a product of the privilege restriction but I can't figure out another route to take. I feel it would be inelegant to build separate CONTROL tables for each user. This would probably work, but I have multiple portal relationships for each control table and would need multiple scripts, multiple layouts, etc If we added remote workers, it would be a nightmare. Ideas please?
  6. Yes, that's what I've ended up doing actually. I just wondered if I was missing something.
  7. tbl.Products has 3 fields: Ref, Agent and Tagged. A number of Products are tagged for an export. From another layout, I want to chart, using ExecuteSQL, the Count of Ref for each Agent who has Products that are Tagged. So at present I have: X AXIS: SELECT Agent from tbl.Products WHERE Tagged is not null ORDER BY Agent ASC Y AXIS: SELECT Count(Ref) FROM tbl.Products WHERE Tagged is not null GROUP BY Agent ORDER BY Agent ASC So my chart shows an ordered. But what I want to do is ORDER BY the count, which means in the SQL I need to output two fields for each SELECT: SELECT Agent,Count(Ref) AS CountRef FROM tbl.Products WHERE Tagged is not null GROUP BY Agent ORDER BY CountRef DESC I there a way to get Filemaker to extract the Agent for the X axis, and CountRef for the Y axis?
  8. Hi, I've been doing SQL for years and don't understand why I couldn't simplify it to that myself! I guess working 12-13 hour days have been catching up with me! However, I was really looking to parameterise it if possible. Is it possible?
  9. Is there any way to make this statement without using IFs? Let( [ $country = "IT"; $region = "Toscana"; $subregion = ""; $town = ""; $result = ExecuteSQL ( "SELECT Ref FROM RealEstate WHERE Country = ? " & If(IsEmpty($region);" AND (Region is null)";" AND (Region = '" & $region & "')") & If(IsEmpty($subregion);" AND (SubRegion is null OR SubRegion is not null)";" AND (SubRegion = '" & $subregion & "')") & If(IsEmpty($town);" AND (Town is null OR Town is not null)";" AND (Town = '" & $town & "')"); "" ; "" )]; $result ) I use a tbl.Criteria table to store multiple records of rules, that I then use to select various groups of real estate listings for a portal export. Due to the way the portal is set up I need to match how they define certain places and supply that data. The Country is always required. However, they define some places without regions or subregions or town names which makes using a parameterised ExecuteSQL hard or impossible it seems. For instance, if the rule is for Vilamoura I need to select only those listings that are defined as having PT, Algarve, no subregion, Vilamoura. For the rule for Tuscany, I need to select listings that have IT, Toscana but any subregion or town. And the rule for Bergerac should only select properties that are explicitly FR, Aquitaine,Dordogne,Bergerac. Here's three records from tbl.Criteria: Country Region SubRegion Town FR Aquitaine Dordogne Bergerac PT Algarve Vilamoura IT Toscana As you can see sometimes there are gaps, and they need to be there in order to match to Rightmove's definition of different places. Here's some records from tbl.RealEstate Ref Country Region SubRegion Town 123 FR Aquitaine Dordogne Jarnac 456 PT Algarve Vilamoura 789 IT Toscana I know I can probably set up IFs to check which kind of search it is in order to create multiple ExecuteSQL statements, but maybe there's a more efficient way to do this in one sitting? I have spent hours fiddling around and Googling.
  • Create New...

Important Information

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