Sage 100

I need to change the status of a bunch of purchase orders to ""Completed"" via VI. Isn't there a way to update the data validation to allow a status of ""X""? Thanks!

Search SC for instruction on modifying the data dictionary. @AlnoorCassim has posted several times. Don't forget to change the last update date (?) field as well

Thanks @JeffSchwenk I saw that but this issue is not changing the ability to write into the status field but changing the validation so it allows an ""X"". I did not see where I could change the validation. I am sure I am not looking at something obvious.

Best to import but the business object may even prevent that...

I thought once a PO qualified as being closed, the status updated automatically.

@KevinMoyes That is correct. The issue they have a ton of PO's several years old that do not ""qualify"" as closed so we are trying to force the status to closed so we can purge them out. I realize the ramifications to purchases clearing etc.

Doesn't the Purge Obsolete utility ignore status?

John I think a VI Export (instead of Import) with Perform Logic that directly updates the Status (not thru the biz object) is the way to go. So you would export out only these old PO's. The Perform would then run only on these POs and update their status. I'll see what I can whip up.

@AlnoorCassim Thanks!

John, I had to write a quick and dirty utility to change the status and the completion date. All you would need to do is enter the CSV filename and completion date. The PO# has to be in the first column. Once that done, you can use the Purge Completed Orders utility to remove them. If you want it, I can send it to you with instructions.

Part 1 of Multi-part answer: Criteria for the **Purge Obsolete utility** in case this could work for you: For convenience let's call the date you select on the screen the _Cutoff Date_ and the POs you select in the grid the _PO Grid Selection_: PO Grid Selection from the screen and Purchase Order Date <= Cutoff Date and Last Purchase Order Date <= Cutoff Date and Last Invoice Date <= Cutoff Date and Last Receipt Date <= Cutoff Date and Last Issue Date <= Cutoff Date So it doesn't care about the Order Status but it's comparing every type of PO date against the Cutoff Date.

@MarcosDeLuna Sure that would be great.

@AlnoorCassim If I am understanding you correctly, my issue is that there have been some receipts posted against these PO's and I just want it to look at the PO date for the cutoff date.

Here you go john. I wrote it for v2017 but it will work for any version where PO is in framework.

Part 2 of Multi-part answer: **Perform Logic** to set in a V/I Export to update Order Status to X and set Completion Date. This would be followed by running Purge Completed PO utility. _The idea here is whatever POs you export those are the ones acted on._ * DO THIS IN A TEST COMPANY FIRST * 1) Create a V/I Export for PO_PurchaseOrderHeader. Have it save to a a file called POs_SetForCompletion.txt or similar. 2) In Data tab make sure PO Number is the first field. All other fields are optional but I would add at least date fields so you cna look at the export file later to see which ones had the Order Status set to Complete. 3) In Select tab you can optionally put in date selection criteria or whatever you want. Either that or choose Run Time Assigned back in the Data tab. 4) Back on Configuration tab click Perform and setup like you see in this screenshot. 5) Take attached file and place in \mas90\PFM folder (create the folder if needed) ** The Main thing to figure out is how you want to set the Completion Date ** Look at Lines 23 - 25 of the pvx file (you can open with Notepad). In my example, I set it to Today. But the on next line, I have it commented out but it's set to Last Receipt Date. So set it to what you want.

@MarcosDeLuna @AlnoorCassim Thanks!!

@AlnoorCassim, Nice. Never would have thought of doing it that way. I did not see lines 23-25 though. I counted 18 lines.

Sorry Marcos I copied it from the wrong folder. Download and look at it again.

Got it. Thanks.

@AlnoorCassim If I want to hard code a completion date, would the syntax be 20180831$ (for 08/31/2018)? Thanks

Verbatim like this below including the quotes: `PO1.CompletionDate$ = ""20180831""`

@AlnoorCassim Perfect. Thanks,.

Don't forget to try on TEST COMPANY FIRST.

@AlnoorCassim Always test first on test co and backup when on live data. Thanks again for your help.

Just musing... about a different way to deal with closing PO's. UDF_FORCE_CLOSE checkbox in PO header. Column post validate script on the UDF, to SetValueNoValidate any required fields (when value = ""Y""). VI job to import into the UDF (and let the script do its thing...). ?

Kevin that's good thinking. However I believe the validation for the Order Status is directly in the business object as opposed to the dictionary. You might get a ""Invalid Order Status for this Purchase order"" as your LastErrorMsg even on SetValueNoValidate. That's why in my Perform and in Marcos' utility it avoids the biz object and updates directly. Now having said that, pretend I didn't say that and just try it :-) (maybe a simple button script)

Does flagging as closed then purging them using above steps allow you to avoid have to edit PO's that have been partially received or received and not invoiced? Does it update purchases clearing for PO lines that are no longer there because of purging?

For that to work the purge utility would have to create data for DTR / GL entries... and somehow I don't think Sage would make that kind of effort. It's my understanding that after purging, you need to do manual GL entries to bring the GL in line with outstanding Purchases Clearing amounts.

It does not update the purchases clearing account.

I believe it was back in Vegas during a user session that Diane Coffman led where she discussed the future of this clean up utility. Wonder what ever happened to those flip chart notes??

Confirmed by Sage I remember that topic well! It was the number one request in Influence the Design in 2014 and has remained important at sessions since then. I have logged, classified, and ranked all of those requests into our database. At this point, all decision are made by Linda Cade. You might want to reach out to her. ??

@AlnoorCassim The button script seemed to work with ABC data. PO was marked as Completed with a date. Super simple! retSet = 0 retSet = oBusObj.SetValueNoValidate(""OrderStatus$"", ""X"") retSet = oBusObj.SetValueNoValidate(""CompletionDate$"", ""20180101"") Before deploying I'd put in place some sort of ""are you sure?"" prompt (with a reminder about fixing the GL), but we get this question often enough it is nice to have a brute force solution when a customer wants one.

Way to go Kev! Now we have 3 - 4 ways of doing this.

Thanks again everyone. This is what makes this group great!

Wow, just wow. I would also put in a loop to verify that the user is part of an Admin group. Easier than just setting up a special panel for the user.

Yes, that is a good idea Jeff. I'd certainly want to wrap a few levels of protection around such a script, and an IsMember role check would fit nicely.

Also, this light bulb just fired off - Us the PO module date for the completion date. User can change that date instead of using a fixed hard coded date.....

(Of course a dynamic date would be better. I just tested something quick to make sure the business object would allow the change to Completed).

Only if it held from PO to PO. IMHO, this might be a monthly exercise so setting it once would be preferable. But then of course, if users could be trained to pay attention to the PO, Received and invoice quantities matching when they post and follow up accordingly, life would be much simpler...

Still thinking on this. I would also add a UDF flag (eg manually closed?) to set when the script runs so that history shows how it completed.

@KevinMoyes Did you ever bring this button script to fruition? I need to do something for a client. PM or call me if this is easier.

I only tested with the proof of concept commands above... not having time to take it any further.