Jump to content

Recommended Posts

I have a Db with 265,000 records. I am attempting to find the Unique Value count of a field called PRODUCT SKU in a table called SALES.

ExecuteSQL (

       "SELECT DISTINCT \"PRODUCT SKU\"

       FROM \"SALES\"";

       ""; ""

)

 

I created a calculation field called SKU COUNT with the above SQL. At the moment the query is running at a pace of about 6 seconds per record. I'm obviously doing something wrong. 

Share this post


Link to post
Share on other sites

ExecuteSQL is a great feature in FM, but it has limitations. I was told I shouldn't use it on a larger than 40K record dataset. However, FileMaker native find is incredibly fast.

 

Also, your SQL query does not produce a count. 

 

This is what you need:

  1. SELECT COUNT (DISTINCT column-name)
  2. FROM table-name

Hope this helps!

Edited by Agnes Riley

Share this post


Link to post
Share on other sites

I'm new to the concept of SQL queries in FMPro so you'll have to forgive me. Every forum post I've found for Counting Unique values in a field give that same SQL code. So I'm not certain I understand what's going on there...

 

I also don't understand the use of "column name"?

LIterally, what I'm trying to do is this. I have a DB with products. It has a product sku field, product description field, product category field, number sold field, etc...etc...

Each sale is a record.

My job is to parse out the data to other individuals so they can Categorize each product sku. All I'm really trying to find out is how many SKUs that haven't been assigned to categories yet. I can do a find on the blank (=) product category field but it will return 156,000+ records because it's looking at the occurrences of the SALES that have no category assigned versus the SKU.

 

Share this post


Link to post
Share on other sites

The column name refers to your field name. 

Your SQL calculation is missing the Count() function. This is not a FileMaker problem but a malformed SQL function problem.

Your calc should be:

ExecuteSQL (

       "SELECT COUNT ( DISTINCT \"PRODUCT SKU\")

       FROM \"SALES\"";

       ""; ""

)

But perhaps if you told us what you are trying to achieve we could guide you better.

 

You also should to fill out the 

  • Location: 
  • Skill: 
  • Version: 
  • OS: 

part of your profile.

Edited by Agnes Riley

Share this post


Link to post
Share on other sites
Quote

LIterally, what I'm trying to do is this. I have a DB with products. It has a product sku field, product description field, product category field, number sold field, etc...etc...

Each sale is a record.

My job is to parse out the data to other individuals so they can Categorize each product sku. All I'm really trying to find out is how many SKUs that haven't been assigned to categories yet. I can do a find on the blank (=) product category field but it will return 156,000+ records because it's looking at the occurrences of the SALES that have no category assigned versus the SKU.

 

 

Share this post


Link to post
Share on other sites

Then shouldn't you be searching in the PRODUCT table as opposed to the SALES table? Or the SKU table perhaps? Link the sales records to the SKUs in the SKU table and then you can search for which ones have children then omit those.

Share this post


Link to post
Share on other sites

Everything is one table....

Share this post


Link to post
Share on other sites

Well, then it's time to learn about relational database structure. I recommend the FileMaker Training series or read about it on Google.

Different entities should be stored in different tables. A SKU is different from the actual product sold. You can sell more of the same product under the same SKU correct? And PRODUCT is different from SALES. So as a minimum you should have 3 tables in your system. Otherwise you basically just have an Excel sheet.

  • Thanks 1

Share this post


Link to post
Share on other sites

As a PHP/MySQL developer, I am unfamiliar with Filemaker's syntax as I have only ever used it as a Prototyping tool for UI/UX in web dev. In fact I wasn't even aware Filemaker supported SQL natively until I searched online for a possible plugin to implement SQL in Filemaker as a workaround to this.

However, this project was dumped in my lap as is. Which as I understand it was imported from a flat text file with only LineFeeds as delimiters from an accounting system written in 1991. 

 

"Different entities should be stored in different tables". <<< Agreed

"A SKU is different from the actual product sold. You can sell more of the same product under the same SKU correct?" << Yes, but I'm not sure what this means in relation to my question?

 

"And PRODUCT is different from SALES. So as a minimum you should have 3 tables in your system. Otherwise you basically just have an Excel sheet." <<< That's almost exactly what this is...but they have a Filemaker (sort of) system and I'm trying to fix their data based on what they have. Which means trying to pull the info out of Filemaker as best I can until I can get what I need to work in MySQL. 

The problem is that in their 50 years of business, they have never categorized any of their products in any meaningful way. The people who recognize the SKU and what that particular product actually is are the salespeople who order and sell them. 

I'm trying to help them get their products categorized in order to build their website which is the actual client project, but unfortunately I've hit this stumbling block along the way. Their salespeople do not understand Excel in any useful way, so my thought was to provide each salesperson an FMPro standalone solution with all of their possible categories available to be assigned to each of their product lines and to extract some meaningful sales data.

Example: 

SKU# 12345A has 30,000 SALES. I need to allow Joe SalesMan to select SKU#12345A and assign it to Product Category A for all 30,000 SALES. This needs to be done in a  manner that will allow him to make as few mistakes as possible. So my thought is a drop down with categories and a Product SKU field that is locked.

SKU#12345B has 5,000 SALES. Bob SalesMan needs to be able to select SKU#12345B and assign it to Product Category X, since he's the only one who knows what that SKU actually is. 

 

So since disseminating the Data amongst the salespeople seems to be the only viable method to get the correct information, I need to parse the data myself in FMPro to know which SKUs have not been assigned yet and to group them for export to give each saleperson their particular set of SKUs to work on...

Share this post


Link to post
Share on other sites

I am definitely not trying to provide them a true RDMS in Filemaker. I'm simply trying to get at what I need to begin the actual project.

Which led me to posting this question in the first place as to why Filemaker didn't seem to want to process my SQL query correctly as I had already tried

ExecuteSQL ("SELECT COUNT (DISTINCT PRODUCT SKU) FROM SALES" ; "" ; "").

and

ValueCount(

ExecuteSQL ("SELECT DISTINCT (\"PRODUCT SKU\") FROM \"SALES\"  " ; "" ; "")

 )
 
 
Which returned only a question mark in the calculated field.

If there's a suggestion for a better method of making all of this happen, I'm open to any ideas that will make this easier for sure.

Also I think there might be some confusion with their naming schema. SALES is the name of their Table in FM. The entire table is named SALES with PRODUCT SKU as a field.

Share this post


Link to post
Share on other sites
11 hours ago, Agnes Riley said:

ExecuteSQL (

       "SELECT COUNT ( DISTINCT \"PRODUCT SKU\")

       FROM \"SALES\"";

       ""; ""

)

Doesn't work either....after creating the calculation field for that statement a window pops up that says "Records Remaining to Process: 265562" and then does nothing....I have to force quit out of Filemaker.

Share this post


Link to post
Share on other sites

It doesn't do nothing, it just takes forever to do. As I said it gets incredibly slow after 40 K of records.

I also said and I may not have been clear that you're better off using FileMaker native search. You have no need for SQL and it's clearly not serving you well.

I recommend you guys hire a FileMaker Developer. You ought to have a relational database. If they have been in business for 50 years they can afford to hire someone who can get this done properly. Otherwise you'll have to learn how to develop in FileMaker, which is doable but it will take you a lot longer. Or you can create a hacked system that someone else will have to fix at some point.

But if you insist, just script your find. You'll still put the SKUs another table to weed out duplicates. You can Google how to do that there are different articles about it.

Then you can search for the unique SKUs.

Edited by Agnes Riley

Share this post


Link to post
Share on other sites

It quite literally does nothing. I've left the calculation running overnight and in the morning it still says Processing Records with the same record count. I know how to find duplicates in FM...I was hoping for a simple script step or calculation that would allow me to count unique values after a search. It seems incomprehensible that this couldn't easily be done. 

 They're not trying to build a Filemaker solution, I just need to get to their data after it's been categorized and collated. FM just happens to be the mechanism that they have to store their Sales data at the moment.

Share this post


Link to post
Share on other sites

I think we're missing a piece of the puzzle. can you post a file?

 

attached demo showing two ways to get a list of unique skus. Also can use value list (old school technique).

uniqueDemo.fmp12

Edited by bcooney
demo file

Share this post


Link to post
Share on other sites

Here's a VERY pared down version of the file also lightly sanitized. The actual file has 256,000+ records in it.

DEMO.fmp12

Share this post


Link to post
Share on other sites

Not sure in what form you need the results. Here's your file back with two global fields that contain a list of unique skus and the count of that field (set by script).

DEMOv2.fmp12

  • Like 2
  • Thanks 1

Share this post


Link to post
Share on other sites

Excellent. Worked perfectly. TYVM...now I'll have to spend some time breaking down the script steps to understand why this works in FMPro but not the other statements. 

Share this post


Link to post
Share on other sites

Take a min of that time to updated your profile

Share this post


Link to post
Share on other sites

I think you and Agnes should take a page out of bcooney's book in how to to treat people looking for help in an unfamiliar language.

Share this post


Link to post
Share on other sites

Dear OneStop, glad you know my name, while you didn't disclose yours. I'm sorry if I offended you. I'm glad bcooney could help. Good luck with your work!

 

And Lee just asked you to update your profile, just like he asks everyone else. That's a basic board requirement. Nothing's offensive about that.

Edited by Agnes Riley
  • Like 1

Share this post


Link to post
Share on other sites

The value list method that @bcooney mentioned is a good one. One I've used often. It relies on something FM is already doing, building an index of values. That index, by it's own nature, is a unique list. I didn't look at her file, but I imagine she is using the function ValueListItems ( ). That would be my route.

Another method is simply looping the records and building a list as you go. If the current record value doesn't exist in the list, add it. Here PatternCount ( ) would tell you if the value is in the list.

Everyone here is just trying to help. No one means any harm. :) Even if it reads that way sometimes. Have fun...look forward to helping you with any other questions you may have. 

  • Like 1

Share this post


Link to post
Share on other sites

First let me say, I'm not offended. It takes quite a bit more than some random forum posts to offend me.

I will say that I've never joined a forum where the moderators pressed me to provide more details including personal details so incessantly. Private messages, forum posts, ad infinitum. Ostensibly under the guise of being able to provide better answers by ruling out software incompatibilities. I always assume the probability of data mining instead.

Secondly, my point was that bcooney, while they didn't address my original question as to issue of the SQL seemingly not working properly, they did however provide a solution in a very short concise exchange. Rather than implying that I should learn about relational databases, rework the client's entire system or advise them to "hire a professional". I clearly outlined the constraints within which I was working and simply needed to get data out of a poorly designed FMPro "spreadsheet".

I apologize if my post sounded cross or angry, it was at the end of a very long and very frustrating day trying to explain to a client set in their ways, why their data is bad and why it requires scrubbing regardless of the fact that they've "been using it with no problem for years". And the idea of paying me to build out a proper solution in PHP/MySQL is simply beyond their comprehension.

I assumed making a count of null values in FM would have been a trivial task. I still don't know why the Query never executed.

Share this post


Link to post
Share on other sites

Ok, starting over. I was not feeling well and sorry, if I frustrated you. I was trying to help. And apparently you know as well as we do a database should be relational.

Nevertheless you wanted a quick and dirty fix and I'm sorry if I came over harsh and didn't provide that.

ExecuteSQL is a somewhat new function in FM. We love it but it's not for everything. It really isn't meant to be used on large sets.

One way to find "NULL" values, is search for value (*) then omit that request in a script. 

Share this post


Link to post
Share on other sites

Everyone has bad days. I was certainly having one myself. No need for apologies at all. I appreciate any and all help.

This client is so frustrating, I'm considering firing them and moving on to a stack of other projects waiting. And it certainly didn't help to have the owners giving me looks of disapproval while I waited over 24 hours for what seemed like a simple SQL query to execute. I killed the process on the machine and reformed my query in every fashion I could think of and it still only returned a ? mark in the calculation field even when it didn't stall or lockup. 

At the end of the day the solution I was provided worked out well and allowed me to finish scripting their data entry process so they don't have to do all kinds of resorts after finding the NULL value in the Category field.  So the sales people can now just select a dropdown category...auto fill all the related records and cruise on to the next SKU. 

 

However, as difficult as they've been, I'm starting to wonder what they're going to be like when it comes time to start building out their site...20 years of doing this has really honed my red flag awareness...lol

 

Anyway thank you again for everything. This is truly an amazing resource for FMPro and Db related discussion. 

 

(P.S.) I'm still not a fan of the intrusive nature of the personal details requests. Lookin' at you mods...

Just a side note...you guys being the experts may already know this, but for us web devs...Filemaker is THE solution for migrating customer's data from one shopping cart system to another or from a POS system to an online cart, or from Quickbooks,  etc...

Being able to do things like easily concatenate file paths with image names is INVALUABLE in saving hundreds of man hours. Create a container field, do some simple scripting and boom...you have the image and where it lives in a new export file.  Doing it in Excel is clunky as all get out. I'm actually surprised no one has ever built a FMPro translation/migration solution that allows you to import data from one cart and rename all the relevant fields to the new cart's schema, etc...

Edited by OneStop
Typos

Share this post


Link to post
Share on other sites

Going back to your original post, there's a big red flag there that hasn't been discussed: it's almost never a good idea to use ExecuteSQL as a calculated field, or any layout calculation (tooltips, conditional formatting etc.). There's been quite a bit written about this, but essentially the deal is that if there are uncommitted records in the table you're searching, the client will attempt to download the entire table from the server. Not a pleasant experience.

Bottom line is, only use ESQL in scripts, where you can control it a little better. When used appropriately it's fine with large data sets. 265k is large-ish but FileMaker can handle millions of records no problem, it just depends on the design.

Since you're familiar with SQL this link will have a lot you already know but you may find some FileMaker-specific tidbits:

https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

About the profile, I share your concerns about collecting personal info. We have to register people with an email address to prevent spam. The rest of the profile is completely optional. However, it really does help us -- a lot! -- to answer questions if we know what version of FileMaker, what platform and what level of experience you have. Also FYI this forum is pretty much run by @Ocean West as a labor of love. I hope the ads cover his hosting costs but I don't know. He is a fellow FileMaker developer who's been supporting the community for as long as FileMaker has been around. I know he takes data privacy seriously.

  • Like 1

Share this post


Link to post
Share on other sites

In this situation, there is no server. Each client has a copy of the system running locally. Yes I know they're violating ALL of the rules of data integrity. Can't be helped as I don't control that situation. ACID isn't even among their vocabulary. They're still using a Flat Text Accounting system written in 1991 on SCO for god's sake. I've been hired to drag these folk into the 21st century. 

 

Since I don't understand FMPro as thoroughly as I apparently ought to... any thoughts on why the SQL wouldn't execute at all?

Also, as to the rest of the profile being optional....yes it says as much but when you have mods sending you PMs and tagging on the end of every post telling you to "finish your profile"...it gets a bit unnerving. 

And just to give you a hint as to my age...I started using Filemaker when it was ClarisWorks 3.0 on a Novell Netware system...And yes I am still a novice because I never spent a second worth of time to delve into it's capabilities. I use it for what it can provide me in time saving as a WebDev. 

Edited by OneStop

Share this post


Link to post
Share on other sites
On 1/12/2018 at 4:35 AM, OneStop said:

First let me say, I'm not offended. It takes quite a bit more than some random forum posts to offend me.

I will say that I've never joined a forum where the moderators pressed me to provide more details including personal details so incessantly. Private messages, forum posts, ad infinitum. Ostensibly under the guise of being able to provide better answers by ruling out software incompatibilities. I always assume the probability of data mining instead...

To ease your mind, no data mining here. This forum exists to help others. No one is making money from it. We are all volunteers. 

The version of FM you are using greatly influences the answer we give. It would be a complete waste of time to tell you about card windows if you are on version 15, etc.

hope today is going better for you!!

Share this post


Link to post
Share on other sites
On 1/13/2018 at 1:30 AM, Fitch said:

Going back to your original post, there's a big red flag there that hasn't been discussed: it's almost never a good idea to use ExecuteSQL as a calculated field, or any layout calculation (tooltips, conditional formatting etc.).

You took the words right out of my mouth, Tom.  Unfortunately, I hadn't had the time to follow this thread as I'd liked.  Wim explains it very well below, link here https://community.filemaker.com/thread/149215?start=0&tstart=0  (although the entire thread is well worth the study).

Quote

That's not the case though... as I have shown in my devcon presentations.  Doing a straightforward SELECT on millions of records takes just a few milliseconds.  Same as doing the exact same query on a smaller table.

The slowdown comes from:

1- complexity added to the SQL statement (JOINs, SQL functions,...)

2- or running the SQL query when you (as the one executing the query) have an open record on the target table (FMS sends you all of the data in the whole table so that the client can resolve the SQL statement - as opposed to the server doing the SQL query)

In both of these scenarios the execution speed will be linear (sometimes even exponential) with the size of the table.

But only in those two scenarios.  So I want to be clear that the size of the table itself does not automatically mean that ExecuteSQL() calls are going to be slower the bigger the table gets.  If your SQL statement is complex, it is going to be slow even on a small table.  If you have an open record on a table with just 10,000 records you will get a noticeable delay.

As to #2: it is the main reason why I do not use ExecuteSQL() calls in field calc definitions, tooltips, conditional formats, visibility calcs and so on: because you have no control over the open state of the record so you have no control over the FMP-to-FMS behavior that will decide if FMS is going to send you all the data...

The slowdown is not in the actual SQL query but in waiting for the client to get all of the data for all of the records from FMS,

OneStop, you're lucky to have such great assistance.  This is why I still ask questions here as well. :-)

My apology for not reading the entire thread so I'll only suggest that using Perform Script on Server is lightening fast if you can use it.  I'm still blown away at the speed differences.  

On 1/11/2018 at 5:14 PM, Agnes Riley said:

I'm sorry if I offended you.

Agnes, you've been quite helpful and respectful throughout.  This forum is lucky to have you assisting.

Edited by LaRetta
Strange that I couldn't respond up against the original post. Oh well.
  • Like 2

Share this post


Link to post
Share on other sites

I love Perform Script On Served, as well.

And thank you for posting this from Wim, @LaRetta. I respect Wim and now I know that it's never the size of the table. It's not live and learn, it's read and learn! :-)

Then again we all know using the right tool in the right way will generate the right result. My instinct told me to never use ExecuteSQL in anything except scripts buy I never pondered why. And the only time I don't get the right result is when my SQL query is malformed.

Share this post


Link to post
Share on other sites

Hmm...I did know about the fact that ESQL's performance suffers if the table that you are querying has an open record. However, I'm not sure PSOS gets around that. I think if that client has an open record, even with PSOS, you still suffer.

Maybe Wim will confirm...

Share this post


Link to post
Share on other sites

Hi Barbara!  As I explained, I really haven't followed this thread and was simply making an observation about Tom's mention of ExecuteSQL() in a calculation and Josh's mention of using a looping script.  Depending upon the need, I thought PSOS was a good mention here.  Nice to see you!

Share this post


Link to post
Share on other sites

 

PSOS doesn't seem to be an option, 

On 1/13/2018 at 3:33 PM, OneStop said:

In this situation, there is no server

Lee

Share this post


Link to post
Share on other sites
On 1/11/2018 at 9:58 AM, Agnes Riley said:

ExecuteSQL is a great feature in FM, but it has limitations. I was told I shouldn't use it on a larger than 40K record dataset. However, FileMaker native find is incredibly fast.

 

That all depends what you use it for. I needed something recently to filter customers within a set of postcodes. There's over 300,000 customers in the database, and I had a list of 80 or so postcodes (the list of postcodes is a list that sits in a radius of specified range around one postcode).

The reason for extracting customer that live in that area is for marketing purposes. I tried using a multikey relationship but that was horribly slow. Exporting that list took about 30 minutes.

I ended up using ExecuteSQL to extract firstname, lastname and email from the customer table with a WHERE Postcode IN ('1000','1001', etc). That finished the job within seconds returning 8,000 customers into variable that I could export in less than a second.

I don't know how I would have been able to do this in a find, aside from creating new find records for 80 postcodes. I don't think it would have beaten ExecuteSQL.

So what you have been told about not using ExecuteSQL on sets larger than 40k records may have been true once, but certainly isn't any more in v16. :-D

 

 

Share this post


Link to post
Share on other sites

The point is that creating a calculation field using ExecuteSQL() is not recommended.

Share this post


Link to post
Share on other sites

I was actually responding to a comment Agnes made at the beginning of the thread, before using ExecuteSQL in a field became a topic. I probably should have mentioned that the ExecuteSQL I used was in a script.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

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