Grouping and looping?

I’d like to blame the turkey, but no, it’s just my non-programmer’s mind.

Our website uses a system of parent and child items. The children are packed in different quantities:

11-401 parent
11-401 full pack
11-401 half pack
11-401 tube

At any given time, the items will either show a status of “In Stock” or “Out of Stock”. The parent item should show “In Stock” as long as any of the children are in stock. Unfortunately, the website software is dumb and doesn’t keep track of the parent stock status in relation to the children. So if we sell out of an item completely, the parent might still display “In Stock” when it should flip over to out of stock. Likewise the opposite. A shipment comes in and the children all regain “In Stock” status, but the parent continues to display “Out of Stock” until we manually correct it.

So. I’m trying to think of a way (perhaps with arrayloop?) to check each item grouping and correct as needed.

Example One:

ItemNum Type StockStatus:
11-401 parent Out of Stock
11-401 full pack Out of Stock
11-401 half pack In Stock
11-401 tube In Stock

So the code would need to recognize that the parent is showing out of stock but this is incorrect because one or more of the child items has stock.

Example Two shows the opposite situation. The parent shows “In Stock” but all of the children are sold out, so the parent needs to be corrected.

ItemNum Type StockStatus:
11-401 parent In Stock
11-401 full pack Out of Stock
11-401 half pack Out of Stock
11-401 tube Out of Stock

FYI there are about 7,000 items to check, so roughly 28,000 records. Ideally the output from this procedure would be a neat list of parent items that need updating, displaying current StockStatus and revised status.

Any suggestions most welcome!

b

PS I don’t think it matters, but on the website each of the child items has its own modified item number. Tubes for example, have a “TB” prefix, so 11-401 packed in a tube is listed as “TB-11-401”. All items on the website have a unique ID number so when uploading corrections of this sort only the ID and the correct field header(s) are required.

This is just a starting point to try since I don’t know for sure your exact configuration in your database. I have used your two example arrays in a variable I call theArray and output the corrected items into the variable theList in the following code.

If array(theArray,1,¶) endswith "In Stock" and
    arrayrange(theArray,2,-1,¶) notcontains "In Stock"
        theList =?(theList="",theList,theList+¶)+
            replace(array(theArray,1,¶),"In Stock","Out of Stock")
Elseif array(theArray,1,¶) endswith "Out of Stock" and
    arrayrange(theArray,2,-1,¶) contains "In Stock"
        looparray theArray,¶,element,index
            if index >1 and element contains "In Stock"
                 theList =?(theList="",theList,theList+¶)+
                     replace(element,"In Stock","Out of Stock")
            endif
        endloop
Endif
message theList

Using the message statement your first array returns:

11-401 half pack Out of Stock
11-401 tube Out of Stock

And using the second array we get:

11-401 parent Out of Stock

So the final list will contain the items with the proper corrections.

Thanks Gary. I think this’ll be easier if you have a chunk of the real database to work with. Easier for me, for sure! Not sure how to send it thru the forum so here’s a dropbox link:

I added one field where changed records could be flagged. Please let me know if anything is unclear and thanks.

Ok, here is the best I could come up with that seems to cover the conditiions you itemized above. This procedure takes 2 or 3 minutes on my iMac to go through your 29000+ records and find/correct the problem “parent” types while keeping a list of the changed items in the changeList variable. Note that my first heavy handed approach of selecting and inspecting these records within the datasheet took 20 or more minutes so, even though this is not record breaking speed, it is a vast improvement over that first attempt.

fileglobal changeList

changeList=""
let theParents=""
let tempArray=""
let outOfStock=""

/* turn on the undo so you can revert and stop screen redraws */
startdatabasechange "ALLRECORDS","ProductStatus Corrections"
noshow

/* get a list of all ItemNumbers that are Out of Stock and remove duplicates */
arraybuild outOfStock,¶,"",ItemNumber,ProductStatus="Out of Stock"
outOfStock=arraydeduplicate(outOfStock,¶)

/* take each ItemNumber that is Out of Stock and check for problems */
looparray outOfStock,¶,element,item
    /* this will get the info for parent & children for the current ItemNumber */
    Arraybuild tempArray,¶,"",ItemNumber+tab()+ItemType+tab()+
        ProductStatus,ItemNumber=element
    /* if the parent is Out of Stock then see if any children are In Stock */
    if tempArray contains "parent"+tab()+"Out of Stock"
        /* if any children are In Stock add corrected parent to changeList */
        if tempArray contains "In Stock"
            changeList=?(changeList="","",changeList+¶)+element+tab()+
                "parent"+tab()+"In Stock"
            /* now correct the parent in the database */
            find ItemNumber=element and ItemType="parent"
            ProductStatus="In Stock"
        endif
    else
        /* this area means the parent is In Stock so we need to check to
           see if any of the children are In Stock as well */
        if arraydeletevalue(tempArray,element+tab()+"parent"+tab()+
                "In Stock",¶) notcontains "In Stock"
            /* if no children are In Stock, add the corrected parent to changeList */
            changeList=?(changeList="","",changeList+¶)+element+tab()+
                "parent"+tab()+"Out of Stock"
            /* now correct the parent in the database */
            find ItemNumber=element and ItemType="parent"
            ProductStatus="Out of Stock"
        endif
    endif
endloop

/* redraw the datasheet, end noshow and display the changeList */
showpage
endnoshow
displaydata changeList

I will give it a whirl as soon as I get free from various familial obligations. My son who is home for the holiday has also just written a python script to do the same thing. I won’t pretend to understand how yours or his works other than in a vague way. He codes for a living but has no interest in Panorama so if I want tools from him they’re usually in python. My great preference is to use Panorama since it is at least familiar even when above my pay grade

Thanks!

b

I have edited the above code to include comments to help clarify my logic (fuzzy?) in addressing your problem. Hope this helps.

Way beyond the call of duty, but greatly appreciated. It works by the way, but you knew that.

Maybe of interest: My son’s python script runs in .11 milliseconds or thereabouts. I asked him to look at your code and although he isn’t a Pan user, he said he thinks the speed difference is because his script uses a dictionary (or dictionaries? He’s not here at the moment to ask.) Anyway I’m assuming–based on no knowledge–that dictionaries in Pan and python work more or less the same, and wonder if the same speed increase would happen if your code also made use of the dictionary route.
Not to say that I can’t wait a couple minutes for it to run–that’s plenty fast enough for my needs. But it made me curious.

If anyone is interested I can post the python script for comparison.

b

I’ll let Jim address that if he wants to do so. I have no interest in further pursuing it. :weary:

1 Like

I’m not that interested in the python code, but I am curious as to how you made the data accessible from python.

Also, I think you should put Gary on the payroll, “way beyond the call of duty” doesn’t even begin to cover the amount of work he’s done here. Chapeau Gary!

Chapeau indeed, not to mention box of chocolates–just tell me where to send them. This forum is the best on the internet, no question!

The data starts as a text file download from our website. Only three fields: ItemID, ItemNumber,ProductStatus.

I then prepped the file briefly in Panorama adding a field to hold the types (parent, full pack, etc.), and stripped off the prefixes and whatnot so that the item numbers would appear to be identical. Then exported the results as a text file which the python script imported and manipulated. So both the python treatment and Gary’s excellent code began with the same prepped text file.

thanks yet again,

b

Well, certainly not a fair comparison for time of completion. You would have to include the time to get the original text file download, your prep time within Panorama and the exporting and importing of the text file into python. This also does not account for the extra step the Panorama X solution includes for changing the erroneous ProductStatus info within the database. :apple:s and :tangerine:s?

I don’t believe the python script runs in 1/10,000th of a second for 29,000 records of data. Even just loading and saving the text file, with no processing, is going to take longer than that, even if you wrote it in C. Even 0.11 seconds seems awfully fast. As Gary says, apples and oranges.

It’s awful fast. I had Owen modify the code so that I don’t need to do any prep in Pan and now it shows about 0.26 seconds. Owen is now home and informed me that I ran the time wrong in the terminal

That said, I’m not fluent in using the terminal nor are any of my staff, so I’ve also tweaked and improved the Panorama version and plan to use it. I didn’t time the Panorama version last time thru but it’s certainly quick enough for our needs. The slowest part is the download of the text info from our website and that’s 10 minutes or longer. So panorama wins on convenience for me even if the python script is faster.

If anyone wants to see the python code, I’m happy to share it. It’s greek to me. Okay, python. Equally opaque.

Here’s a screen shot from the terminal. It calls the python script, opens the file from the website (StatusExport), and then saves the output.

Continuing the discussion from Grouping and looping?:

Two quick and hopefully very easy questions:

1- How/where do I add another field (ItemID) to the code so it will be included in the output?

2- How would I replace the displaydata with an export file? I tried using changeList but can’t get it to work so I’m missing something. Again.

thanks much!

The above code needs to be slightly reorganized to do the find command before we add the changed info to the changeList variable so we can get the current related value of ItemID. We then can add that value to end of the data being appended to the variable. The variable is saved as a file at the end of the code and you can alter that path to wherever you prefer as well as change the file name as needed. Here is the adjusted code:

fileglobal changeList

changeList=""
let theParents=""
let tempArray=""
let outOfStock=""

/* turn on the undo so you can revert and stop screen redraws */
startdatabasechange "ALLRECORDS","ProductStatus Corrections"
noshow

/* get a list of all ItemNumbers that are Out of Stock and remove duplicates */
arraybuild outOfStock,¶,"",ItemNumber,ProductStatus="Out of Stock"
outOfStock=arraydeduplicate(outOfStock,¶)

/* take each ItemNumber that is Out of Stock and check for problems */
looparray outOfStock,¶,element,item
    /* this will get the info for parent & children for the current ItemNumber */
    Arraybuild tempArray,¶,"",ItemNumber+tab()+ItemType+tab()+
        ProductStatus,ItemNumber=element
    /* if the parent is Out of Stock then see if any children are In Stock */
    if tempArray contains "parent"+tab()+"Out of Stock"
        /* if any children are In Stock add corrected parent to changeList */
        if tempArray contains "In Stock"
            /* correct the parent in the database */
            find ItemNumber=element and ItemType="parent"
            ProductStatus="In Stock"
            /* now add new info to changeList
          changeList=?(changeList="","",changeList+¶)+element+tab()+
              "parent"+tab()+"In Stock"+tab()+ItemID           
        endif
    else
        /* this area means the parent is In Stock so we need to check to
           see if any of the children are In Stock as well */
        if arraydeletevalue(tempArray,element+tab()+"parent"+tab()+
                "In Stock",¶) notcontains "In Stock"
            /* if no children are In Stock, correct the parent in the database */
            find ItemNumber=element and ItemType="parent"
            ProductStatus="Out of Stock"
            /* add the corrected parent to changeList */
            changeList=?(changeList="","",changeList+¶)+element+tab()+
                "parent"+tab()+"Out of Stock"+tab()+ItemID
           
        endif
    endif
endloop

/* redraw the datasheet, end noshow and display the changeList */
showpage
endnoshow
filesave "~/Documents/transfer.txt",changeList

Note that I have not retested this so there may be an error - you’ll soon find out I’m sure. :worried:

There was an error asking for another endif which I inserted. But when I run the new version, it doesn’t output anything. Text file is blank. When I run the earlier version of your code, it displays the corrections using displaydata. I added displaydata to the new version and it also came up blank.

Rather than filling this post with 100 lines of code, I’ve saved each version of the code in a text file and zipped them onto dropbox:

I tried to compare them but there are enough changes that I’m not sure which might be interfering with the output. Sorry!

b

This is the last version I will post on this subject. It has been tested and works fine on my end. Here it is:


changeList=""
let theParents=""
let tempArray=""
let outOfStock=""

startdatabasechange "ALLRECORDS","ProductStatus Corrections"
noshow

arraybuild outOfStock,¶,"",ItemNumber,ProductStatus="Out of Stock"
outOfStock=arraydeduplicate(outOfStock,¶)
arrayfilter outOfStock,outOfStock,¶,array(import(),1,tab())
looparray outOfStock,¶,element,item
    Arraybuild tempArray,¶,"",ItemNumber+tab()+ItemType+tab()+
        ProductStatus,ItemNumber=element
    if tempArray contains "parent"+tab()+"Out of Stock"
        if tempArray contains "In Stock"
            find ItemNumber=element and ItemType="parent"
            ProductStatus="In Stock"
            changeList=?(changeList="","",changeList+¶)+element+tab()+
                "parent"+tab()+"In Stock"+tab()+ItemID
        endif
    else
        if arraydeletevalue(tempArray,element+tab()+"parent"+tab()+
                "In Stock",¶) notcontains "In Stock"
            find ItemNumber=element and ItemType="parent"
            ProductStatus="Out of Stock"
            changeList=?(changeList="","",changeList+¶)+element+tab()+
                "parent"+tab()+"Out of Stock"+tab()+ItemID
        endif
    endif
endloop
showpage
endnoshow
filesave "~/Documents/transfer.txt",changeList

Fini! :partying_face:

Perfect–THANKS!