BIC is great, but if it is a document that is wanted; the process of being able to run off Delivery Note followed by Invoice – is superior for dispatch rather than going off into another menu to run delivery notes in a BIC report.
I was faced with a Client who sells shoes – but by the carton. He’s a wholesaler into the market and imports the shoes in cartons from China. China is NEVER wrong – so don’t unpack the box simply operate a turnkey DISTRIBUTION business.
We have set up the client with segmented inventory in case you were wondering. But I have attached the delivery note and the Invoice for the purposes of Illustration.
The Client has pre-printed stationery with Logo – so you guessed ; he doesn’t want to change his stationery!!!- Or his layout – much.
This could apply to any business where their goods are packed into pallets, trays etc, and I would think fairly common out there.
Let’s just say at the outset that obviously one needs to be able to use either UOM (stocking Units) or at the very least pack codes. Often a customer gets confused with the UOM when they sell in a quantity – or they have been used to converting from the bulk to the individual.
So let’s go through some of the techniques that were used on the Delivery Note and duplicated to the invoice:
1) Totalling the Qty column :
Actually simple – when you know how. Insert a DBCalc field where you want the total : in our example it was in the header of the invoice/delivery note.
Thee DB calc function can be set to sum or count take an average etc. These functions can be set by right clicking and selecting “calculations” from the popup menu.
When you have placed the field onto the document use the data components toolbar to instruct the DBcalc what field it is summing/counting.
Where a field is placed in order on a document before the fields that would calculate you need to tell the calculation to “Look ahead”. Right click; on the pop-up menu check/select the “look ahead” option.
The concept of ORDER is very important when using the Report Writer – if your machine is slow enough you will be able to see these calculations taking place in real time as you preview the documents or runt he reports.
You can preview the report/document now and you should see the correct sum come up in the field that you have added.
2) Adding in variable to calculate packing quantities :
Any other calculation or logical argument is added to the report/document as a “Variable”. Variables are actually extremely useful and quite powerful tools to add or calculate translate or choose as to what should be represented on the report. They can also be hidden if not needed to be printed or viewed by the user or his customer. I have two examples of this going forward – stay with the whole process!!!
In the same manner as you inserted the DBCAlc insert a Variable onto the document grid – in this case I needed a simple calculation to work out how many “cartons” would be shipped for each different inventory item.
The variable itself must be converted to a “double” field in order for it to calculate or compile when running the report.
Then click on the Calc Tab: Locate the applicable fields from the database from those listed on the Toolbars under the tab “Data” on the right hand side of the screen.
You can drag and drop them onto the script screen to the left. If you do this there is no naming mis-transcribes. Simple mathematical functionality can be used for simple calculations – such as here.
We have used a pack code onto the inventory master with a numeric value for the pack sizes. This si used to divide the qty supplied. As each item can be possibly different we have done this calculation for every detail line on the Invoice. Both these fields are “doubles” to be used in the calculation.
The calculation is represented as such:
Value := plInvLines[‘fQtyLastProcess’] / plInventory[‘PackSize’]
This has been placed in the Group header section of the Delivery Note. The default “timing” properties have been left as Traversal – as it runs – but I have changed the reset properties to Group End. Simply put the calculation stops or re-sets at the end of the section. This allows (I think) any other calculation to run after it.
Once saved you can preview your delivery note/invoice to see that the calculations are running properly.
2) Summing a variable to total the packing quantities :
The next logical requirement from the customer was to have the total number of cartons tallied in some section of the Delivery Note. This required a second variable to add up all the individual line variables. Variable 2.
Insert a second variable in the same manner as the first. The calculation for summing a variable is as follows: Value := value+ Variable1.value Be sure to id your variable accurately.
Using this calculation and placing the variable below variable 1 will result in the correct calculation – again this is a timing thing for the form to know what it should deal with first. Placing it above variable 1 , will result in it excluding the last line of the document , even if that is the only line on the document. ( Try it!!!) If this is what you require then you should be OK with the one variable to do the summing. You will need to set it’s timing to traversal and then the reset to Group End – so it runs after the variable in the details section of the invoice/delivery note. You will also need to tell it to “look ahead”.
3) Summing the a variable to a total appearing in the header section of the form:
However if you are working with pre-printed stationery and your customer would like to have this total appear in a “box” on the “header” area of the form – you need to use another technique. This technique is called by Brett ( at Pastel’s Durban branch) “confusing it into submission” !! Brett is a programmer – so he has that kind of 3rd dimension logic that I was missing – anyway it worked – so I offer it here because more than one person has been convinced by frustration that this is impossible!! ( I hear you!!!!)
If you examine the rtm file – you will see that my variable 2 was inserted into the detail section of the document, and made invisible ( simply uncheck the visible properties on the popup menu). The timing was set to “Traversal” and Reset at Group end – the same as Variable 1. If you make this visible it will return quantities to you of the total number of cartons on every line. ( Hence I have made it invisible.) But the calculation is right. It lies to the right hand side of the variable1. I have introduced now a 4th Variable (no 3 was used for something else) that totals variable 2. The calculation command is the same, with the change to the variable:
Value := value + Variable2.value
But the timing of this variable starts on the Group End (where Variable 2 finishes) and resets on the report end. It returns essentially the same values as does the Variable 2 – but I can place it above variable 2 – even in the header section!!! When you run this report (you will need to invoice as it’s based on processed quantities) you can actually see the end quantity come on the screen in real time. Nonetheless it works!!!
4) Using a variable to control string information appearing on the form
Finally, you can use a variable to govern or choose specific information appearing on a form. Remember that although the fields you are comparing are string :- tha variable field needs to be made into a “double”.
In this example the customer has “standard” packs and numbers of shoe sizes in that pack which we have used as a description field. Then he has a different combination on the solid packs – which is inserted on the fly in the UDF per line on the sales order. One document could have both : so we have used the If, else command to control this. The variable (no 3) was inserted in the detail line. The calculation for this of:
if (plInvLines[‘ulIDSOrdTxSTSolidPack’]) = ‘Solid Pack’ then
Value := (plInvLines[‘ucIDSOrdTxSTSizeRange’])
else Value := plInventory[‘Description_2’];
was actually copied from the only calculation that exists on the generic invoice in the detail section for the choice between using the Exclusive or Inclusive prices.
A useful technique that Brett taught me was simply to copy and paste that entire syntax and just replace the fields – and the values that I knew were available in that choice of fields. No further editing was required.