If, copy, paste script

[enquirerfm]

I have a file of records (this is a large file).

 

It might look like this (just a short extract) - say there are 90 different 'refs':

 

Fields:

Ref   Batch no.   Description                       Cost $

120   1                Yellow jersey                   160.00 (this is in fact the total for the whole of Ref 120 e.g. 8 items so one might suppose that ea. item costs $20 each - in practise they don't they each have other values)

120   2                Red jersey                           0.00

120   3                Blue jersey                           0.00

.

.

120   8                White jersey                         0.00

 

130   23              White hat                             10.00

130   23              Red hat                                  0.0

130   23               Black hat                                0.0

 

 

I want to insert the $ total e.g. 160 and 10  into the other cost fields for the same ref nos - I realise that this is not going to be right the right cost but I will thenstart to then do other calculations. I tried setting up an If with a loop but it became rather complicated. I started by organising the file so that all the 'refs' were in order with the cost > $0 in the first row (as above).

 

Then:

 

[ If Cost > 0 ]

Copy [select; Cost ]

Go to Record/Request/Page [Next]

If [Cost = 0]

Paste [Cost]

Else

Go to Record/Request/Page [Next]

End If

 

But this doesn't work because it won't loop and it is missing the End If anyway. If anyone could suggest something...? Thank you.

[Lee Smith]

I think the easiest way to do this is to use the Find and Replace. Find the Ref number and Replace Field Content.

 

You could use a Script with the loop step, but it would be more complicated than I think you need.

 

You might find this video helpful. Scripted Looping Find

[Aussie John]

I hope I have understood your request....

 

What I have done in the past is make a second Table occurrence and self join using "ref". place the cost field from the second Table into your layout  and when you add a value to it all of the fields all with the same Ref number will populate!!!.

 

Using this self join enables you do a count the ref field ( based on the 2nd table) to give you a number of items with the ref. Divide into the cost and you get the unit cost.

[Fitch]

Try it like this:

 

If [Cost > 0 ]
Copy [Cost ]

Else

Paste [Cost]

End If
Go to Record/..[Next]