Cardbox Talk

I am looking for help to write a macro, let me explain. I have a database with 3 columns, quantity, cost each and total. I need a macro that could take the number in the quantity column multiply it by the cost each column and provide a total. Is this possible?

I do have this macro in place for adding figures in a field, but have no idea how to modify it.

total=0
if Fields("COST_TOTAL").Textlength > 0 then
amounts=Split(Fields("COST_TOTAL"),CHR(10))
for each amount in amounts
amount = Replace(amount,"£","")
total = total + amount
next
end if
EditRecord
Fields("COST_VALUE")=FormatNumber(total)
SaveRecord

Alpha, here's the modified macro. You'll have to change the field "NONAME1" to your own field name, and you must tell the macro which character(s) you use as delimiters for your columns.
This is the variable "splitchar", which is now ", ", i.e. a comma followed by a space. So, if your columns look like this:

4, 8, 32
2, 6, 12
etc.

the macro will work correctly.
If you link this macro to the "Save record" button, the field will be automatically updated every time the record is saved.

Charles

splitchar=", "
FieldContent=""

if Fields("NONAME1").Textlength > 0 then

lines=Split(Fields("NONAME1"),CHR(10))
for each line in lines
figures=Split(line,splitchar)
number=figures(0)
cost_each=figures(1)
total=number*cost_each
FieldContent = FieldContent & number & splitchar & cost_each & splitchar & total & vbCrLf
total=0
next
end if

EditRecord
Fields("NONAME1")=FieldContent
SaveRecord

Charles, thankyou for your assistance, however I am not very educated when it comes to macros, can I be cheeky and ask you to help me complete it with the below information.

My quantity field is called Q2
Cost each field is Q5
Total field is Q6

Can you help apply them to the macro?

In this case the macro is not going to work at all. You wrote that you had three COLUMNS, not three FIELDS. What the above macro does, is splitting a single field into its constituent parts and do some calculations with these parts. In the end the parts are joined again.

If your fields contain only one amount/quantity, then the correct macro is much more simple, because you can refer to the field as a whole, no need for splitting:

EditRecord
Fields("Q6")=Fields("Q2")*Fields("Q5")
SaveRecord

When you are already editing the record, and I suppose you would be, omit the line "EditRecord".

On the other hand, if each field contains more than one line, the macro becomes more complicated. I'm willing to help out, but not before I'm sure I have all the relevant details. So, if the new macro isn't the right one, please tell me EXACTLY how your data is organised.

Charles

Charles, thankyou, I will attempt to explain as clearly as I can.

We have a database which comprises of 3 fields, it is designed to calculate a quick quote to a client

The first field is to input a quantity and we have named the field Q2, we would be inputing more than one figure in this field

The second field is a cost each field which we named Q5, as above this will have more than one figure in it

Finally we have a Total field, named Q6

See below for how we intend to use the fields

Quantity Cost Each Total

1 £25.00 £25.00
4 £10.00 £40.00
5 £ 1.00 £ 5.00

Is this acheivable?

Well now, that is clear as cristal. Below you'll find the macro, and I'll explain what it does.

Assuming that you are editing the record, it first saves it. For the macro to work correctly (and for your quote to be correct), there must be an equal number of lines in Q2 and Q5. There may not be empty lines at the end of the fields, and saving the record simply strips the fields of any empty lines. Then the macro start editing again.
The fields Q2 and Q5 are split into seperate lines. The field Q6 is made empty, and an empty variable "totalstring" will be the new field Q6.
Next the lines are counted, so the macro will know how many times it should do the calculation.
The variable "counter" makes a loop that runs as many times as there are lines. Within this loop the "£" is removed, the total is calculated, the "£" is put back and the result is attached to the string "totalstring".
When the loop has finished, "totalstring" is written to Q6 and the record is saved.

----------

SaveRecord
EditRecord

quantities=split(Fields("Q2"),chr(10))
costs=split(Fields("Q5"),chr(10))
Fields("Q6")=""
totalstring=""

lines=0
for each quantity in quantities
lines=lines+1
next

for counter = 1 to lines
costs(counter-1)=Replace(costs(counter-1),"£","")
totalstring=totalstring & "£ " & quantities(counter-1)*costs(counter-1) & vbCrLf
next

Fields("Q6")=totalstring

SaveRecord

I just remembered that you would like to have your amounts nicely formatted.
Replace the original line that begins with "totalstring=totalstring etc." with the one below, and your amounts will always have two decimals.

----

totalstring=totalstring & "£ " & FormatNumber(quantities(counter-1)*costs(counter-1),2) & vbCrLf

Charles, this works an absolute treat, and I thank you for this. Am i to late to throw one more thing into the mix.

I have been asked to create 1 final field in this format that you kindly wrote the macro for, this field is called Q62. The purpose of this field is to provide a grand total for all the figures in field Q6.

Is there a line you can add to the macro created below that will finally add the figures in Q6 and display the result in Q62?

Your help is much appreciated

And one last thing Charles, how can I get the macro to run after pressing save, at the moment I edit the record, input the data and then have to click tools, run macro then the macro. Can the macro be attached to the save button?

Here is your revised macro. You can attach this macro to the Save command as follows:

Edit the native format (Edit > Format > Edit)
Click: Tools > Toolbar > Editing Records
Find the "Save" button and change its action from "built-in command" (i.e. save the record) to "play a macro" and choose this macro (I don't know what name you've given it).

Do the same for the CTRL-s keystroke:
Click: Tools > Keyboard
Find the CTRL-S keystroke and change its action from "built-in command" to "play a macro".
Don't forget to make this keystroke work only when you're editing a record (see left corner of the pop-up window).
And just to make sure: add the keystroke CTRL-SHIFT-S and attach the macro to this keystroke as well.

Save the format.

What happens now is that when a user makes a new record or edits an existing record the macro will run and calculate the quote.

------

SaveRecord
EditRecord
quantities=split(Fields("Q2"),chr(10))
costs=split(Fields("Q5"),chr(10))
Fields("Q6")=""
totalstring=""
subtotal=0
grandtotal=0

lines=0
for each quantity in quantities
lines=lines+1
next

for counter = 1 to lines
costs(counter-1)=Replace(costs(counter-1),"£","")
subtotal=quantities(counter-1)*costs(counter-1)
grandtotal=grandtotal+subtotal
totalstring=totalstring & "£ " & FormatNumber(subtotal,2) & vbCrLf
next

Fields("Q6")=totalstring
Fields("Q62")="£ " & grandtotal

SaveRecord

------
Please note that a user could edit a record and use the "Save as new" command, in which case the macro would not run. The macro could provide for this, but I've encountered some irregular behaviour in Cardbox. As soon as this has been sorted out, I'll post another reply to correct this. For the moment you can use the abovr solution.

Charles

Thank you Charles, it works extremely well, I know you have shown me how to do this but the total box Q62 is showing the total as e.g. £200 not £200.00, is this easy to fix?

Yes, off course. I forgot.

Fields("Q62")="£ " & FormatNumber(grandtotal,2)

Here's a solution for "Save as New".

If you have already attached the macro to the buttons/menus and keystrokes, please add ",1" to the name of the macro. So, if your macro is called "save" and you told Cardbox to run "save" when the user clicks the button or menu (or CTRL-S) then it should now be: "save,1" (omit the "").
You should of course use the name you actually gave to the macro.

Attach the macro to the SaveAsNew button as well, but now add ",2" (play macro "save,2").

The macro will now be aware of the button/menu the user has used, and it will either save the record or save it as a new record.

Replace your macro by the following:

do
cr=right(Fields("Q2"),1)
if cr=chr(10) then Fields("Q2")=left(Fields("Q2"),Fields("Q2").Textlength-1)
loop until cr>chr(10)

do
cr=right(Fields("Q5"),1)
if cr=chr(10) then Fields("Q5")=left(Fields("Q5"),Fields("Q5").Textlength-1)
loop until cr>chr(10)

quantities=split(Fields("Q2"),chr(10))
costs=split(Fields("Q5"),chr(10))
Fields("Q6")=""
totalstring=""
subtotal=0
grandtotal=0

lines=0
for each quantity in quantities
lines=lines+1
next

for counter = 1 to lines
costs(counter-1)=Replace(costs(counter-1),"£","")
subtotal=quantities(counter-1)*costs(counter-1)
grandtotal=grandtotal+subtotal
totalstring=totalstring & "£ " & FormatNumber(subtotal,2) & vbCrLf
next

Fields("Q6")=totalstring
Fields("Q62")="£ " & FormatNumber(grandtotal,2)

if commandline = "1" then
SaveRecord
else if commandline = "2" then
SaveRecord cbxSaveAsNew
end if
end if

From Egbert to Charles & Alpha: a separate forum is available for these macro problems
It is called "macros and programming". Maybe it is more relevat to continue overthere: http://forums.cardbox.com/forums/MACROS