Cardbox Talk

Hello, I am new to Cardbox, and have just joined a company using it. I have been asked to find out if it is possible to run a macro (i have no clues on macros) that would be able to add figures in a field we have called selling_prices, then display the figure in another field called total.

Please can you help, I cannot find a manual in our office and everyone that could help me has decided to be on leave

Pause "Select and tag records"
SelectTagged

x = 0

n = RecordCount

For i=1 to n 'Start loop through records

  x="0"+x+Fields("selling_prices")
NextRecord
  Next

LastRecord

EditRecord
Fields("total")=x
SaveRecord

It could be written differently and more efficiently, but the above might let you see more clearly what is happening. Hope it helps.

Mary

Thankyou for this, i have corrected two of my own mistakes, one of the fields is actually selling_price and the other is value. With these changes made, i now get an error type mismatch:[string: ""] position the macro line 4, character 3.

Would this mean anything to you?

Do i also need all the gaps in between the lines like you have shown above

Mary,

This is the macro I am using, the error is mismatch:[string: ""] position the macro line 10, character 3.

Pause "Select and tag records"
SelectTagged

x = 0

n = RecordCount

For i=1 to n 'Start loop through records

  x="0"+x+Fields("selling_price")
NextRecord
  Next

LastRecord

EditRecord
Fields("value")=x
SaveRecord

The error message is given when a field that is used in a calculation is empty.
Modify your macro as follows:

if Fields("SELLING_PRICE").Textlength > 0 then x = x + Fields("SELLING_PRICE")

This way the macro will look at the field SELLING_PRICE to see if there's anything in it, before it tries adding the field to the total.

It is by the way a good habit (sorry, Mary) to give your variables meaningful names, so don't use "x" but for instance "total", not "n" but "SoManyRecords", etc. (or anything you like).
When macros tend to increase in length and complexity, using meaningful names keeps them manageable.

Charles, thank you, this has helped, however, and once again I apologise as i am not literate in programming, if i have one field (selling_price) that has more than one cost in it, i.e. £50.00, £250.00 i get the following error

Type mismatch [string: "50.00 £250.00"] position in macro line 10, character 48

Re: Using meaningful names for variables

Charles,

I agree with you about using meaningful names for variables and I use meaningful names when necessary. Sometimes when the variable is only for short temporary use I find x can be adequate.

Sossageking
Why would you have more than one cost in the field? The macro won't work if you have more than 1 amount in the field.

Mary

Sorry, as i am new i am not sure why, but they have one field example below

Selling price
£
£
£
£
£

Total £

However it is just one large field, and not individual fields next to the pound signs, the total field is a seperate field

Here's your solution. Put these lines within the loop that goes through your records, or use them seperately as a macro that "does" only one record.
This macro does the following:
First it looks if there's anything in the field SELLING_PRICE.
Then it takes all the prices in the field and calls them "amounts". The macro splits these amounts into seperate lines, i.e. separate amounts.
Then it goes through all the separate amounts ("for each amount in amounts"), it removes the "£", and adds what is left to the total, which is "0" to start with.
At the end the record is edited, and the field "VALUE" is updated.

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

This kind of macro is typically one that should be used (see previous topic) as an "on save" macro. In that case the field VALUE would be automatically updated every time the record is saved. If this macro needs to be started manually, anyone could change the field SELLING_PRICE which leaves you with an incorrect field VALUE.

Charles, when i play the above macro i get a type mismatch: 'amount' line 6 character 1 error

P.S.

Mary, I didn't mean to criticise your use of short variables. But as Sossageking is absolutely new to macros, the examples need to be as clear as possible. It is better to get Sossageking started on the right foot.

Regards, Charles

Are you sure that there's nothing else in the field besides the amounts like you wrote before? I've tested the macro, and it worked correctly.
Any text besides "£" would cause the error you mentioned.

Charles, it now works and thankyou very much. When it calculates the total it shows the end figure as 69 not 69.00 it may seem minor, but is there a way of making it show 69.00.

Again, thanks for your understanding

Charles, i think the problem i had i was not on record one of one, i now realise that you have to tag the record you want the macro to run on, i tried the macro on record 1851 of 1855 and the error occured, i moved to record 1855 of 1855 and the macro worked

You can format the numbers by changing the macro as follows:

Fields("VALUE")=FormatNumber(total)

The FormatNumber command looks at your "regional and language options" in your control panel to see how you would like to have your numbers formatted. If the change does not make your numbers look like "£123.00", have a look at those settings.

Running this macro has nothing to do with tagging records. The macro works on the current, i.e. visible record, no matter whether it is tagged or not. If the macro works on your record 1855, but not on 1851, there must be something different in the latter. Please compare them, and if you like, report back.

P.S.

Mary, I didn't mean to criticise your use of short variables. But as Sossageking is absolutely new to macros, the examples need to be as clear as possible. It is better to get Sossageking started on the right foot.

Regards, Charles

Charles,

No offence taken! You are quite right of course. I was taking the lazy way out in my example to Sossageking, as I had the ready-made example in my macros database, where I store useful examples in case I need them again and can't remember how to do something, or just to save me typing.

I very appreciate all your very useful contributions to the forum.

Best regards,

Mary

Charles, it worked an absolute treat, I am now a hero and i Thankyou