Read CSV, Update Material Queue and then Process the Record

[Ricky90 (Ricky Horne)]

Hi, I have a CSV file from our WMS system (seperate to Epicor). The 3 columns it contains are MtlQueueSeq, To Bin and Quantity.

Id like to schedule a function to read the CSV files in a directory every 5 minutes, update the To Bin in the MtlQueue table and then process the record with the Quantity leaving any outstanding qty in situ without user interaction.

To give context im attempting to integrate Modula Lifters with EpicorERP ensuing ‘complete’ automation. (At least thats the task ive been given).

Hopefully someone can help out as i can export a CSV file from a BAQ in a Function but importing, i do not know where to start.

[klincecum (Kevin Lincecum)]

Kinetic, Application Studio, Server Files (Saas)

In a function: //ref: Ice.Contracts.Lib.ServerPath //using System.IO; CallService<Ice.Contracts.ServerPathSvcContract>(serverPathSvc => { List<Ice.Lib.ServerPath.Types.PathInfo> pathInfo = new List<Ice.Lib.ServerPath.Types.PathInfo>(); pathInfo = serverPathSvc.GetPaths(Epicor.ServiceModel.Utilities.SpecialFolder.CompanyData, "", false); if(pathInfo.Count > 0) { string path = $"{pathInfo.First().FullName}\\testing.txt"; File.WriteAllText(path, "In t…

Should get you started.

Anything else you need help with?

[Mark_Wonsil]

If you’re a cloud developer and afraid of file systems like me, you could also write an Epicor Function that updates the ToBin in the MtlQueue and processes the record. Now create a PowerShell that reads the CSV after the export and does a POST to your Efx using Invoke-RESTMethod. It removes the dependency on the File system and removes all of those messy details from your Kinetic server code.

[klincecum (Kevin Lincecum)]

I skip the filesystem when I can, and even when it’s hard, memorystream is your friend

[Ricky90 (Ricky Horne)]

Well so far so good via Postman, updating the MtlQueue table.
Im struggling with the process button. Trans Type is STK-SHP.

The help states to use Move Material program but im unsure where to find that except for within Epicor and that looks to relate to Jobs.

[Mark_Wonsil]

@Ricky90

Im struggling with the process button

Trace is the only way to know!

[klincecum (Kevin Lincecum)]

Not related but came up in search for relevant gif, couldn’t not share:

[Ricky90 (Ricky Horne)]

I think i found it under ERP.BO.IssueReturn.SVC and custom method PerformMaterialMovement.
I need to load the mtlqueue data into the function so i can build the Issue Returns dataset in order to move the mtl queue record. Thank you for pointing me in the right direction!

[Ricky90 (Ricky Horne)]

i decided to change it up a bit.

I will use Powershell to read the CSV and call the Epicor Function but i decided to have a go using the widgets for the updating.
am i better using UpdateExt from a Function rather than Update method?
Also, should i use row mod as A or use U if the row already exists and im changing a field.

[klincecum (Kevin Lincecum)]

@Ricky90

am i better using UpdateExt from a Function rather than Update method?

It doesn’t really matter as long as you have the proper data for the call.
You’ll end up at the same place in the end.

[klincecum (Kevin Lincecum)]

@Ricky90

Also, should i use row mod as A or use U if the row already exists and im changing a field.

Editing → “U”
Adding → “A”
Deleting → “D”

[Ricky90 (Ricky Horne)]

Not sure why this isnt working as i expected… I have another directive doing similar on another table and that works…

The update is tableset is bound to the getbyid variable.

Then i use a fill table by query, pass all the data from the same tableset and change the to Bin and row Mod to U.

[Ricky90 (Ricky Horne)]

a simple bit of code wont update the record…
The correct data is passing into the function as i used an exception to show the 3 variables are available

No errors in event viewer…or in postman when testing the function.

[klincecum (Kevin Lincecum)]

You should probably use the business object instead of the Db context.

[klincecum (Kevin Lincecum)]

If you are dead set on using the Db context though, you need to call:

Db.Validate() in a BPM, or Db.SaveChanges() in a function before txScopeComplete()

In a function, you will also have to enable read/write for that table, and in the tables screen
on the right side, allow edit/update/whatever it says lol.

[Ricky90 (Ricky Horne)]

so near yet so far.

This code works in postman to process a material queue record.
In Epicor however the first line in the body shows an error in event viewer.

The error given is Ice.Api.Exceptions.ApiException: Parameter plNegQtyAction is not found in the input object.

below is the working code in postman, im struggling to find the issue.

[klincecum (Kevin Lincecum)]

No need to call rest from inside.

If you do continue, IssueReturn should be an array.

[Ricky90 (Ricky Horne)]

I know but im struggling with adding a record to the IssueReturns method so i thought id try the rest method as i got that working ouside of epicor.

[klincecum (Kevin Lincecum)]

I understand how you feel. I’ll see if I can futz with it.

[Ricky90 (Ricky Horne)]

what are the long term issues of using rest inside epicor?