Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

About OneStop

  • Rank
  • Birthday 07/27/1975

Profile Information

  • Gender

FileMaker Experience

  • Skill Level
  • FM Application
    16 Advanced

Platform Environment

  • OS Platform
  • OS Version
    Windows 10

    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.

    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...

    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.

    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.

    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.

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

    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.

    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.

    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.
  10. SQL SLOW

    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...
  11. SQL SLOW

    Everything is one table....
  12. SQL SLOW

    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.
  13. SQL SLOW

    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.
  14. I have a number field that some data was imported into incorrectly. Some of the numbers are supposed to be negative numbers like -10 but instead they were imported as 10- How would I go about moving the - to the beginning of the field in front of the integers?

Important Information

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