David Ford

Of course...delete the first one. then replace it with the downloaded one. Sometimes the problem in seeing the most obvious thing is to not being able to see the most obvious thing! I will try that. Again thanks

The have a web based system that they can goto anytime, i just assumed the morning would be best. they click a button in the reports section that gives them the current inventory snapshot. they can choose to download in csv or xlsx format. As for your question about 'insert from url' , that is what i use for the api method. I don't believe the csv or xlsx file is stored on their server. If so that would solve the problem. I will have to check. As it is, the developers emailed and said the parameters needed can be added to the api for me to try. But that sometimes takes weeks. They are a fast growing pos system, and are short staffed. Just like everywhere in the world. And I never thought about deleting the file after download. That would work unless they have 50 already in the downloads folder. Then it would get named filex(51).csv. Thanks again comment, always wise feedback

Yes, they can do a snapshot at any time they want. Ideally first thing in the morning. As for automating it, they have an API but it is ridiculously slow and I have to download the entire inventory snapshot all 7900 products. Then converting the json to a csv is not super quick as there are somewhere in the neighborhood of about 125000 lines of json. I have requested some parameters be added so i an filter out the products I need...approx 475. Much faster. The user can get a snapshot of those 475 products as they are in their on category and the pos software allows the users to do that. Again that seemed a good idea by setting up a recurring import but, the filename issue has come up. I do understand why they do it and did beforehand. I was hoping for some ideas. Thanks

I am trying to set up a recurring import from a file downloaded each day. Windows adds (1) or (2) etc as a suffix to each file with the same name and therefore, my filename used in the recurring import fails. I am hoping that the end user does not have to change the way they do things on their computer. They should just click the daily update on the site they are using download the file. Then i have filemaker do its magic and import the file. any suggestions to overcome this. p.s I hope this is not a redo of a previous topic. I could not figure out a good search term(s) for this. Dave

Here is the sql that works, the results are below. just have to figure out if this is convertable to filemaker. WITH Ranked AS ( SELECT Category,ProductName,SKUCLEANED, SUM(quantity) AS QTY, ROW_NUMBER() OVER(PARTITION BY CATEGORY ORDER BY SKUCLEANED) AS RN FROM POSSALES GROUP BY CATEGORY, PRODUCTNAME, SKUCLEANED ) SELECT Category,ProductName,skucleaned, QTY FROM Ranked WHERE RN <= 10 ORDER BY CATEGORY, QTY DESC RESULTS: CATEGORY PRODUCTNAME SKUCLEANED QTY Beverages Tweed - Bakerstreet & Ginger - 1x355ml 320021_1X355ML___ 206 Beverages Deep Space - Deep Space - 1x222ml 320025_1X222ML___ 159 Beverages Mollo - 5.0 - 1x355ml 320039_1X355ML___ 153 Beverages XMG - Tropical Fruit - 1x236ml 320042_1X236ML___ 147 Beverages Little Victory - Sparkling Dark Cherry Beverage - 1x355ml 320033_1X355ML___ 88 Beverages Tweed - Houndstooth & Soda - 1x355ml 320001_1X355ML___ 76 Beverages House of Terpenes - Limonene & Sparkling Tonic - 1x355ml 320036_1X355ML___ 59 Beverages Houseplant - Grapefruit Sparkling Water - 1x355ml 320002_1X355ML___ 44 Beverages Little Victory - Sparkling Blood Orange Beverage - 1x355ml 320031_1X355ML___ 26 Beverages Ripple by The Green Organic Dutchman - Dissolvable Balanced Powder - 4x0.45g 320017_4X0.45G___ 5 Capsules Redecan - Gems 5:0 - 15 caps 101071_15CAPS___ 51 Capsules Redecan - Gems 5:5 - 15 caps 101421_15CAPS___ 37 Capsules Redecan - CBD Gems - 15 caps 101480_15CAPS___ 17 Capsules Tweed - CBD Softgels - 15 caps 101096_15CAPS___ 8 Capsules Tweed - Penelope Softgels 10 mg - 15 caps 100029_15CAPS___ 4 Capsules Tweed - Penelope Softgels 2.5 mg - 15 caps 100273_15CAPS___ 3 Capsules Redecan - CBD Gems + 101861_15CAPS___ 2 Dried Flower RIFF - Subway Scientist - 3.5g 100079_3.5G___ 118 Dried Flower Solei - Balance - 1g 100063_1G___ 54 Dried Flower LBS - Sunset - 3.5g 100013_3.5G___ 54 Dried Flower LBS - Sunset - 1g 100013_1G___ 52 Dried Flower Tweed - Hindu Kush - 1g 100019_1G___ 51 Dried Flower Tweed - C-LAND - 1g 100023_1G___ 42 Dried Flower Good Supply - Jean Guy - 15g 100080_15G___ 15 Dried Flower LBS - Sunset - 7g 100013_7G___ 14 Dried Flower Solei - Balance - 3.5g 100063_3.5G___ 7 Dried Flower Tweed - Afghan Kush - 3.5g 100031_3.5G___ 3 Edibles Bhang - THC Milk Chocolate Bar - 1x10g 330028_1X10G___ 208 Edibles Bhang - THC Dark Chocolate Bar - 1x10g 330029_1X10G___ 131 Edibles Foray - Peach Mango Soft Chews (2-Pieces) - 2x5g 330007_2X5G___ 85 Edibles Foray - Raspberry Vanilla Soft Chews (2-Pieces) - 2x5g 330010_2X5G___ 60 Edibles Chowie Wowie - Balance Solid Milk Chocolate - 1x16g 330026_1X16G___ 58 Edibles Affirma - Watermelon Soft Chew - 1x3.2g 330050_1X3.2G___ 49 Edibles Foray - Salted Caramel Chocolate Square - 1x10g 330003_1X10G___ 25 Edibles Aurora Drift - Peach Serene CBD Soft Chews - 5x4.3g 330022_5X4.3G___ 25 Edibles Chowie Wowie - THC Solid Milk Chocolate - 1x16g 330024_1X16G___ 24 Edibles Affirma - Sour Cherry Soft Chew - 1x3.2g 330049_1X3.2G___ 16 Extracts Original Stash - OS.Hash10 - 2g 310007_2G___ 104 Extracts San Rafael '71 - OG Chemdog Live Resin - 1g 310021_1G___ 65 Extracts Hiway - Hash - 2g 310022_2G___ 56 Extracts Canna Farms - BC Bubble Hash - 1g 310006_1G___ 44 Extracts Greybeard - KISH Terp Slush Live Resin - 1g 310018_1G___ 37 Extracts Greybeard - SLK Terp Slush Live Resin - 1g 310019_1G___ 33 Extracts 48North - Traditional Pressed Hashish - 1g 310014_1G___ 29 Extracts Fireside X - Glueberry Shatter - 1g 310013_1G___ 18 Extracts 7ACRES - Sensi Star Flower Rosin - 1g 310023_1G___ 13 Extracts Fireside X - Wappa Shatter - 1g 310001_1G___ 10 Oils Redecan - Reign Drops 30:0 - 30ml 100490_30ML___ 84 Oils Redecan - CBD Reign Drops - 30ml 101481_30ML___ 60 Oils Redecan - Reign Drops 15:15 - 30ml 101482_30ML___ 31 Oils Solei - Free Oil - 30ml 100420_30ML___ 19 Oils MediPharm Labs - CBD 50 Plus Formula Oil - 20ml 101885_20ML___ 12 Oils Twd - Indica Oral Spray - 20ml 101311_20ML___ 4 Oils Twd - Sativa Oral Spray - 20ml 101312_20ML___ 2 Oils Pure Sunfarms - Pure Sun CBD Oil 1:10 - 30ml 101595_30ML___ 2 Oils Tweed - Penelope Oral Spray - 20ml 101315_20ML___ 1 Oils Pure Sunfarms - Pure Sun CBD Oil 1:30 - 30ml 101596_30ML___ 1 Pre-Rolls Good Supply - Jean Guy Pre-Roll - 1x1g 100074_1X1G___ 248 Pre-Rolls Good Supply - Jean Guy Pre-Roll - 7x0.5g 100074_7X0.5G___ 206 Pre-Rolls RIFF - Subway Scientist Pre-Roll - 1g 100073_1G___ 179 Pre-Rolls RIFF - Subway Scientist Pre-Roll - 7x0.5g 100073_7X0.5G___ 101 Pre-Rolls Trailblazer - Glow Stix - 1x0.5g 100752_1X0.5G___ 59 Pre-Rolls Tweed - Hindu Kush Pre-Roll - 5x0.5g 100412_5X0.5G___ 42 Pre-Rolls RIFF - DT81 Pre-Roll - 1x1g 100997_1X1G___ 40 Pre-Rolls Trailblazer - Flicker Stix - 1x0.5g 100750_1X0.5G___ 24 Pre-Rolls RIFF - DT81 Pre-Roll - 7x0.5g 100997_7X0.5G___ 19 Pre-Rolls Trailblazer - Spark Stix - 1x0.5g 100751_1X0.5G___ 14 Seeds 34 Street Seed Co. - Pineapple Express Seeds - 5 Pack 102003_5 PACK___ 8 Seeds Humboldt Seed Company - Magic Melon (Autoflower) Seeds - 5 Pack 102000_5 PACK___ 7 Seeds 34 Street Seed Co. - Bubba Kush Seeds - 5 Pack 102004_5 PACK___ 6 Seeds 34 Street Seed Co. - Triple Scoop Seeds - 5 Pack 102005_5 PACK___ 5 Seeds 34 Street Seed Co. - Pink Lemonade Seeds - 4 Pack 101390_4PACK___ 3 Seeds Tweed - Bakerstreet Seeds - 4 Pack 100968_4PACK___ 2 Seeds 34 Street Seed Co. - AK-47 (AUTO) Seeds - 5 Pack 101832_5 PACK___ 2 Seeds 34 Street Seed Co. - Mango CBD 1:1 Seeds - 5 Pack 101831_5 PACK___ 1 Seeds CRG Pharma - Charlotte's Diesel Seeds - 4 Pack 102006_4 PACK___ 1 Seeds CRG Pharma - Wedding Cake Seeds - 4 Pack 102007_4 PACK___ 1 Topicals Solei - Balance - 75g 340021_75G___ 16 Topicals Eve & Co. - The Boss Bath Bomb - 140g 340016_140G___ 12 Topicals Nuance - CBD Eucalyptus Bath Bomb - 270g 340040_270G___ 7 Topicals Latitude by 48North - Sex Pot Intimacy Oil - 25g 340019_25G___ 2 Topicals Solei - Free Unscented - 75g 340023_75G___ 2 Topicals Tidal - French Vanilla Lip Balm - 5g 340038_5G___ 1 Topicals Proofly - Water-Based Unisex Lubricant - 25g 340033_25G___ 1 Vapes Good Supply - Pineapple Express 510 Thread Cartridge - 1g 300024_1G___ 70 Vapes Kolab Project - Strawberry Ice Sativa 510 Thread Cartridge - 0.5g 300038_0.5G___ 67 Vapes Redecan - Trainwreck Redee 510 Thread Cartridge - 0.45g 300081_0.45G___ 41 Vapes Kolab Project - Blackberry Cream Indica 510 Thread Cartridge - 0.5g 300037_0.5G___ 39 Vapes Good Supply - Blue Dream 510 Thread Cartridge - 1g 300023_1G___ 35 Vapes Good Supply - Pineapple Express 510 Thread Cartridge - 0.5g 300024_0.5G___ 32 Vapes Good Supply - Blue Dream 510 Thread Cartridge - 0.5g 300023_0.5G___ 29 Vapes CANACA - THC Distillate 510 Thread Cartridge - 0.5g 300110_0.5G___ 13 Vapes Redecan - Zktlz Redee 510 Thread Cartridge - 0.45g 300121_0.45G___ 13 Vapes Foray - Mango Haze Balanced 510 Thread Cartridge - 0.5g 300036_0.5G___ 8

very close..except that I have Category > Subcategory. If only category, it works well. But some of my MainCategory's have 30 Sub Categories. I will look at the script to see if I can modify it. As for the executesql, i was thinking about using a subquery like this or something like this and this by no means is accurate. SELECT category) FROM sales WHERE category IN (SELECT category, productname, Sum(quantity) FROM sales FETCH TOP 10 ROWS ONLY WHERE Sum(quantity) > 1)

We have a report that summarizes data by category, then subsummarizes by product sku and shows the total sold for that product. We are trying to limit the report to 10 in each category. If there are less than 10 then show them all of course. The data itself is imported from an api in json form, then converted to a csv and converted to records. There are approx 30000 records currently as the api sends us back line items from a pos system. I thought about using the executesql function, but can't quite get it. Dave

the later of the two. I have an existing spreadsheet that we use (pic 1) Pic2 is a portion of the paysheet we give them. We would like to convert it to a database. It works in excel, but lots of room for errors. Usually we have lots of error checking as these contractors are very finicky. If we miss a load we hear about it.

we are trying to implement a solution where we use a shared solution (only two computers) setup. How would I need to distinquish sales (invoices) from each machine. I think the Get(persistantId) would work but I thought I read that this function doesn't work in a shared environment. Only local versions of the file work for get(persistantid). I tried to implement this and it seems to be that's the way it is indeed..I set up a script to capture the id of the machine when the program is opened on and it does capture the id on the (host) main file but upon opening the remote (client) the script does not kick in. Any Ideas on how to implement this. Is there a way to have filemaker recognize that I am opening a remote file and to activate the script. The host machine is only used for filemaker, there is other software on there but they only use it for hosting this file. The machines are used as 1 in the kitchen and one in the lounge. The lounge sends food orders to the kitchen printer. I have to be able to distinguish between lounge sales and kitchen sales. So i thought that if I capture the persistent id from the machine at the time of start up in the morning and store it for use thoughout the day, then for each sale used that value. DAve