XML files in Priority

Two new programming projects landed on my desk today. That's not to say that they were completely new, but rather they had progressed from the conceptual stage to the prototype stage. Each one will require further development but I'm very satisfied with my progress today.

The first was reading barcodes with a mobile barcode scanner; the idea is that we will issue each worker in one department with a personalised scanner, each one with the worker's id. Every day the workers will be issued with a page listing all the tasks which they have to perform, where each task has its own barcode. The barcodes are read on the factory floor and are broadcast to a docking station which is connected to a computer running a specific screen in Priority. As a result, we can know on-line what has been produced and who produced it. This has been forced upon us because according to the standard times in each work order and the number of work orders completed, we seem to be overstaffed by nearly a factor of two in this department.

The second project is more complicated: this involves reading an XML file which is created by a computerised design program which is supposed to contain sufficient information for me (or rather, the program that I am writing) to create parts within Priority and to create a bill of materials. I've done similar projects in the past for two other design programs, but the incoming data was somewhat lacking. It looks like we are going to make a large investment in this program (both money and time) and finally we have the possibility of tailoring the interface to our requirements.

I sent the representatives a sample XML file which contained some of the data that I require, and they sent me back a full XML file which contains much more information than I require at the moment. I knew that I had once written some form of procedure in Priority for reading XML files but I couldn't find it. Then I remembered that I had previously written a blog entry on reading XML files, and once I found this, by the date I could find what I had previously written.

I tried to use what I had previously written, but this didn't work for unknown reasons. As I wrote then, although there is some mention of XML files in the documentation, as usual this is so opaque as to be useless.  Maybe I now have a newer version of the documentation, for this time it made a bit more sense (even though there is a mistake in the code); instead of writing an interface, I could use an external program which deals with all the XML parsing, presenting me with a table where each tuple has a row number (not important for me), a tag (i.e. the XML tag) and the value of the tag.

It didn't take me long to adapt the example to what I needed; one line per tag is somewhat awkward when I want to create a tuple consisting of several tags, but this was easily overcome. So quite quickly I had a program which read the XML file and saved the parsed version in an existing table (or work area, as Priority terms it). The next stage was to take the information in this table and turn it into the format required for creating parts and a BOM; this is somewhat tedious and finicky, but I've done it before so I copied almost word for word my original code into this new program, and soon I had a program which achieved this second stage.

There is one more stage which needs to be done in the program (GLOB_TOPSOLID.XML) but this will require the representatives and our engineering people to define exactly the required data. 

The lesson learned today is to use the XMLParse program instead of mucking around with a complicated interface.

The third way, continued

I had another example of finding a third way solution today, although this one is more convoluted and it would be hard to explain. Again there was a situation where one 'side' wanted something whereas the other 'side' wanted something which was mutually incompatible. 

But I found a solution. This 'third way' stuff seems to be good. 

Thinking outside of the box: the third way

I want to document something which happened to me last week, not in order to show how wonderful I am or to pat myself on the back, but in an attempt to learn something and internalise a way of thinking, a way to solve one type of problem.

First of all, the background. Priority has a type of data known as 'business opportunities', otherwise known as presales. The salespeople open these opportunities, recording the customer and the approximate value of the opportunity. When the opportunity reaches the status where the customer sends in his order, automatically a project is opened from the opportunity [this is a non-standard enhancement that I added] and the scope of management changes. After all, a customer order is a 100% promise of further work and so we should know how much work is going on the books and when it is going to be produced.

Priority has two kinds of customers: temporary and permanent. A permanent customer is what we normally think of as a customer: there are orders, delivery notes, invoices and receipts. Naturally a permanent customer has an account in the financial 'books'. A temporary customer, on the other hand, is the complete opposite: no orders and no financial record. About the only data types that are suitable for temporary customers are business opportunities and price quotations; we can hand out price quotations like chocolates, but only a certain percentage will turn into orders and so we don't want to open permanent customers for those that don't. A temporary customer still has all the contact details so in a sense, we're not losing anything.

This is where we have the conflict: what happens when a business opportunity for a temporary customer reaches the status which causes a project to be opened automatically? It's not possible to open a project for a temporary customer, and so when this happens, I get a phone call from a salesperson saying that no project has been opened - or worse, the salesperson opens the project manually (after the customer has been converted from temporary to permanent) and doesn't fill in key fields correctly.

My knee-jerk reaction when I heard about this was to prevent opportunities for temporary customers reaching the critical status. The sales people were up in arms, for apart from anything else, they had no way of signalling to the accounts department that the customer status needed to be changed - this was done by a message being sent when the opportunity reached the critical status. 

So we had a situation with two mutually opposite requirements, with no real way of solving the problem. A third option is required. My usual technique at times like these is to go for a walk; getting away from the computer generally allows a solution to pop up. It's also good to get some exercise, especially in these cold and rainy days.  

After walking up and down the warehouse aisles a few times, a solution did appear. Somehow it must be signalled that the automatic opening of the project must be delayed until the customer becomes permanent. My first instinct was to save the opportunity number in a table and then have a program traverse this table periodically. A few more aisles gave me a better solution: there is no need for a table; instead a flag can be set in the opportunity. Once I had this idea, it wasn't difficult to write the program that opens the project; most of the code was based on the original code that executes when the opportunity changes status.

I was disappointed in that none of the sales staff appreciated - or even understood - the solution. On two successive days, different sales staff contacted me to say that no project had been opened; I told them to wait a little and that soon they would receive an email saying that a project had been opened for a given opportunity. As I had written this in an explanatory email, I can conclude that either people don't read my emails (quite probably) or that they don't understand them. After all, sales people have to sell (they have skills that I certainly don't), and in the same way that I don't understand the ins and outs of selling and of the finer points of our products, they don't understand anything of my world and the constraints under which I have to work. 

But we're not here to talk about how the idea was executed in Priority, but rather how to find a solution when there seem to be two mutually opposite requirements. Step back from the problem and try to find a third way! Don't fixate on those requirements; almost certainly there will be a way of getting around them.

Another record @ 50 - Hot Rats

When writing about four records which were made in 1969, which I heard in 1969-70 and still listen to today with enjoyment, Frank Zappa's 'Hot Rats' was inexplicably left out. Thinking about it today, there are three reasons why I didn't think about this record: it's by an American and is almost completely instrumental, but the crowning reason is that there was no continuation. I have purchased uncountable albums by Fairport, Van der Graaf, 'Crimso' and the Beatles, but this is the only record by Frank Zappa that I ever purchased and the only one which I enjoyed. I did listen to other records that he made, but the 'satirical' (or scatalogical) lyrics and general style turned me off. This record really is unique in his catalogue.

I think that it's also relevant to state that it was the only one of the five which was made with a 16 track tape recorder (although VdGG's 'After the flood' was also recorded on 16 track). All the others were recorded with 8 track machines, which whilst allowing greater freedom that the previous 4 track machines ('Sgt Pepper' not withstanding), were still somewhat restricting (although not necessarily so for Fairport, who didn't indulge/require multiple overdubs). The wiki article explains how the 16 tracks allowed multiple instrumental overdubs by Ian Underwood, resulting in a very rich sound.

'Peaches en regalia' of course is an excellent overture, but I enjoy the closing track, 'It must be a camel' even more; there's something wonderful about those piano chords. Zappa's son, Dweezil, has been touring the 'Hot Rats' album, and somewhere he wonders how on Earth papa Zappa come up with that music.

The only thing that I dislike about the digital version is that there are several versions of it! Zappa felt free to edit his performances after the original record was released and as a result the version that I have is slightly different to the one that I remember (bought in April 1970). Apart from a few extra minutes added to 'The gumbo variations' (I would have preferred that several minutes be removed from this track), there is a very noticeable edit (to me) in 'Willie the pimp', and I'm fairly certain that bits of 'Son of Mr Green Genes' have been remixed, as I don't remember the horns being so prominent at the end.

Granddaughter #2 standing

Granddaughter #2, aged 13 months, is now standing, but she is not yet walking unaided. For some reason, she always seems pleased to see me, and was very upset one evening after I brought her sister home and turned to leave.

Priority tips: clever report hack

Today someone asked for a report which shows per part how many have been sold in a given time period and for how much money. That's fairly simple. The request, though, had a twist: there should be the possibility of showing how much per week or how much per month. Does this mean that I have to write two very similar reports which differ only in how they are aggregated?

A clever hack occurred to me which allows me to write only one report which will dynamically display data either by week or by month. Normally I would write a procedure which consists of the following steps (in reality, each number is multiplied by 10, so step 1 is actually step 10):

1. Collect detailed data and save in a temporary table
2. Ask the user to choose an option
3. Go to that option
4. Report for detailed data
5. Go back to stage 2
6. Report for aggregated data per week
7. Go back to stage 2
8. Report for aggregated data per month
9. Go back to stage 2

My idea was that stages 6 and 8 could be one stage with one shared report. But how to do this in practice?

The first problem was the 'choose' step, step 2 above. Both the weekly and monthly options should have the value 6 (or rather, 60) so that the 'goto' step could jump to the correct report. But then how can the report know how to aggregate? My solution was to use the value 61 for the weekly report and 62 for the monthly report, then insert a code step between the 'choose' step and the 'goto' step as follows

:$.C9 = 10 * (:$.C0 / 10);

:$.C0 is the value returned from the 'choose' step; it will be either 40, 61 or 62. The simple code above stores a truncated version of this number in :$.C9, which will be either 40 or 60. This number is used as the parameter for the 'goto' step and not :$.C0, as I normally would write.

The next stage in the hack is to turn stage 60 into a code step and stage 70 into the report step. In the code step, I assign a dynamic name to the report and a dynamic name to a field in the report (this has been documented since Feb 2018).

 

/* dynamic report and column titles! */
:COLTITLES = 1;
SELECT ENTMESSAGE ('$', 'P', :$.C0) INTO :TITLE FROM DUMMY;
:REPCOLTITLE.100 = :TITLE;
SELECT ENTMESSAGE ('$', 'P', 10) INTO :RTITLE FROM DUMMY;
:HTMLFNCTITLE = STRCAT (:TITLE, ' ', :RTITLE);

This piece of gobbledegook means the following:

1. Use dynamic column titles
2. Read what would be called a 'resource string' in Delphi number :$.C0 into the variable 'title'. As :$.C0 holds the value returned from the 'choose' step; it will be either 61 or 62, so resource string 61 is 'week' and resource string 62 is 'month'. 
3. Set the title of column 100 in the following report to be the value of 'title'.
4. Set the variable 'rtitle' to be the value of resource string 10 ('sales by part by ').
5. Set the report title to be rtitle + title, i.e. 'sales by part by month' or 'sales by part by week' (the variables are in reverse order because this is done in Hebrew).

Finally we get to the report itself. There are two important columns here, the first naturally being number 100 whose title has been set dynamically. This should hold either the week number or the month name; this is complicated slightly because the week number is an integer whereas the month name is a string. This does not faze us: turn the integer into a string then select according to the value of :$.C0 which is passed as a parameter to the report.

(:C0 = 61 ? ITOA (WEEK (INVOICES.IVDATE)) :
DTOA (INVOICES.IVDATE, 'MM/YY'))

Very easy. Normally this would be a sorting key in the report, but that's not a good idea as 1/19 would be followed by 10/19, then 11/19, then 12/19 then finally 2/19 - the date is a string, not a number. So the sorting key has to be a numerical representation: not a problem for the week as this is already an integer. The month/year combination is turned into a sortable integer by extracting the year and multiplying by 12, then adding the month. This is written as follows

((:C0 = 61 ? WEEK (INVOICES.IVDATE) : :
YEAR (INVOICES.IVDATE) * 12 + MONTH (INVOICES.IVDATE))

This is quite a good hack and I should add it to the syllabus of my 'developing for Priority' course. As it happens, I have a teaching appointment for this evening when I intended to talk about complicated reports, so this is ideal timing.