Thursday, October 30, 2014

Another good idea

Just by chance, I had two very good ideas regarding Priority on Monday. It doesn't normally work like that so don't read too much into the coincidence of dates. Although on the other hand, my brain must have been highly activated that day which allowed me to see solutions which I had previously missed.

We have a business unit which is ... problematic, to say the least. Their method of working is unique and their workers aren't exactly the sort of people who take naturally to an ERP program. I know that I've mentioned this business unit before, but at the moment, I can't find any of those references.

This business unit derives most of its income from creating industrial floors. A building will have some kind of floor, but for industry (and also for basketball courts), a special floor covering is required. These coverings are created by mixing together various plastics, lacquers and similar products then layering them on the existing surface. I don't pretend to understand this and I don't really need to understand it. As far as I am concerned, they are taking raw materials and turning them into a finished product. The problems are that this process takes place at the customer's site and that there are no fixed quantities for a square metre of floor.

Several months ago, I overhauled their business processes as executed within Priority in an attempt to make things as simple as possible for them whilst at the same time allowing all the data to be recorded as if they were working in a more standard manner. As mentioned in the previous paragraph, 'production' takes place at the customer's site. This means that raw materials have to leave our warehouse and be transported; this fact has to be represented in the inventory table. The amount of raw material sent may not correspond to the amount needed for the current month; in other words, if a project is going to straddle months, there may not be a correspondence between the month in which the inventory is sent and the month in which the work is performed. The work can also be performed over several months.

Priority allows inventory to be marked with a status; the most normal status is 'goods', but there exists the possibility of marking the inventory with a status which is equivalent to the number of the customer. This way, we can tell how much inventory has been delivered to each customer's site. This seemed to be sufficient, but a few days ago, a request was made to differentiate between projects: apparently one customer has two separate but concurrent projects and it's not possible to differentiate what inventory belongs to which project.

This had me stumped for a while, but then I remembered that Priority allows one to define 'locations' or 'sub warehouses' within a warehouse. We don't use this capability which is probably why it didn't spring to mind. But once I considered 'locations', I realised that this was the answer. We have defined one (virtual) warehouse which holds all the inventory which has been delivered to customers' sites; the inventory is differentiated within this warehouse by its status. I added a little twist to this: each inventory movement will now be against not only this warehouse but also against a specific location (which naturally is the project's number). Thus we can always know how much inventory has been delivered for a specific project.

Actually implementing this was slightly more complicated than I expected, although a great deal of the implementation time was actually spent of fixing a bug which I had unwittingly introduced about a month ago.

[SO: 3663; 2, 15, 35
MPP: 574; 0, 1, 6]

Wednesday, October 29, 2014

Importing purchase orders

For the past few days, I've been wondering what I was doing a year ago. I have read my blog entries for that period but they don't really provide a good understanding. In order to remedy this for the coming years, I have decided to record some of my daily activities, in a more detailed way.

On Sunday (a workday here in Israel), I had a discussion with someone at work. She is charged with entering into Priority a customer's purchase orders; this customer can have more than ten orders a day, so this work takes up a fair amount of time. She wanted to know whether I could create an automatic interface for these orders. As this customer resides in the same database space as we do (a story in itself) and uses the same part numbers as we do (for obvious reasons), I can develop a program which will export their purchase orders to a file, then develop another program to import the data from that file. Due to permissions, this interface has to be separated into two (in other words, I do not want that this customer has access to our database and they don't want that we have access to their database).

I started work on this idea on Sunday afternoon and completed a first version on Monday morning. The export part was not problematic although it did include a new twist: a prior interface which I wrote like this exported comma delimited files and here I wanted a tab delimited file, but this was easily overcome.

I have been aware for some time that Priority has the capability to read tab delimited files into what is termed a 'load table', but I had never done this myself. I looked at an example and discovered what I needed to do. Actually this example led me slightly astray: the file contains two different types of line (headers and details) and I got the impression from the example that I could define two separate 'maps' for reading the input file, depending on the line discriminator. I tried this once and immediately saw the problem as strange data was placed into the wrong fields in the load table. I then redefined the 'map' (and redefined the output format) and this time the data was read from the external file into the load table correctly.

I then wrote a program which would take the data from the load table and import it into the customer orders table; I've done this sort of thing several times in the past so I had no problems. As a result, by Monday lunchtime I had a complete 'suite': a program which exports purchase orders into a file and a program which reads that file and creates customer orders. Each file consisted of one purchase order only.

Yesterday during my evening walk, I had many useful thoughts about this 'suite': primarily, I wanted to extend it so that the file could contain multiple purchase orders. This would mean that the person running the export program would do this once a day and the person running the import program would also do this once a day: this would reduce the overhead to a minimum. I also considered how the first person would receive feedback from the second person, specifically showing what the customer order number was for each purchase order.

This morning I started work on those ideas and discovered that whilst the ideas themselves were good, almost all the implementation details were wrong! Adding multiple purchase orders to the file and reading them were not problematic, but I discovered that the conversion of the data from the load table into the customer orders table was over-complicated. After simplifying this stage, I was able to load all the purchase orders for one day in a minute!

The program includes sending a report by email, showing the results of the interface. I have done similar things in the past, but the report produced has generally depended on one datum (for example, an order number) which had been stored in a special location, whereas here I wanted to send a report containing multiple data (order numbers). The solution had come during yesterday evening's walk - link the report to the load table (the interface which loads the data into the customer orders table updates the table with certain critical data). This idea worked perfectly.

But I also discovered that I had thrown a little part of the baby out with the bath-water: I had no means of checking whether a purchase order had already been converted into a customer order (something which existed in the original one order version). It took a while to figure out a solution for this, but of course I did. At the moment, I'm not convinced that I have chosen the best (or the recommended) method of doing this, but at least it works. This reminds me again of Alan Turing not performing a literature search. 

Hopefully this new system will go into operation on Sunday.

On a different subject, I received late last night the first response from my supervisor regarding my literature survey; I only saw the letter this morning. The letter contains only highlights; detailed comments will be in the draft which he is sending my by courier. This afternoon I will write a non-specific response. At least this activity is moving again, after having been on hold for the past ten days.