loop with global fields

[toshog]

hi all,

i have this problem:

have a global field - number.

have a loop that is supposed to add to the global field an amount - another number filed - with each record. it doesn't do it. the loop is more or less like this:

Loop

set Field ["theGlobalField", "theGlobalFirld + amount"]

Go to Rec..... [next]

End Loop

why it doesn't work?

any help appreciated...

[BobWeaver]

Did you remember to go to the first record before you enter the loop?

Did you make sure that all the pertinent records are in the found set?

What exactly is happening?

[CobaltSky]

Hello toshog,

Not wishing to throw in a spanner, but the effect of your script (when you get it working properly) will be to produce the total of the values in the 'amount' field in all the records in the found set.

I wonder if you'd considered using a summary field instead? A summary field defined as 'Total of amount' would achieve the same outcome without the need to run a script.

Whilst there may be a few cases where the use of a summary field would not be ideal (eg where you want the value to persist after the found set changes), in most cases it offers an easier and effective option.

[toshog]

i've been having problems accessing the fmforums yesterday... and today too...

anyway...

yes, i set up initial values in the first record... i'll post the whole script to make it clear what exactly is what am i trying to do... i wish i can copy it from the FM, but...

Sort [Restore, No dialog]

Go to Rec... [First]

Set Field ["letterHolder", "incomeOptions"]

Set Field ["globalCalculation", "amount"]

Go to Rec... [next]

Loop

If ["incomeOptions = letterHolder"]

Set Field ["globalCalculation", "globalCalculation + amount"]

End If

If ["incomeOptions =/ letterHolder"]

New Rec..

Set Field ["amount", "globalCalculation"]

New Recor....

Go to Rec.... [next]

Set Field ["letterHolder", "incomeOptions"]

Set Field ["globalCalculation", "amount"]

End If

Go to Re... [Exit after last, Next]

End Loop

there it is. the whole point of it is to generate one big list of records

sorted by a mark (incomeOptions) and and summ the amounts for the records

until the mark changes and then the records with the next mark etc etc...

both letterHolder and globalCalculation are global fields...

the script as is works fine but it doesn't sum the amounts it just takes

the first one after resetting the globalCalculation value and puts that as

the summ... globalCalculation is a number global firld and amount is a number field..

any help with this will be appreciated....

thanks...

[Ugo DI LUCA]

looking at your script step by step, with pause, there seems to be a problem here :

Set Field ["amount", "globalCalculation"]

New Recor....

Go to Rec.... [next]

Set Field ["letterHolder", "incomeOptions"]

New Record + Go to next record...

There is no next record after the new record

Maybe I'm wrong. I made the sort on the income options

[CobaltSky]

It is not clear why you are creating new records in this script. Except for the field you set within the script, these will be blank records, which does not seem very useful, plus they will be interfering with the sequencing of the loop.

New records are placed at the end of the found set or the current group in a sorted set, and since the script will have just entered a new sort group (incomeOptions =/ letterHolder) before calling the New Record/Request command, it will have (at the least) skipped to the end of that group.

Aside from this, you should check that the incomeOptions and letterHolder fields are of the same data type - if they are not, the test will behave erratically.

[toshog]

the new records are just for visibility... they shouldn't have anything in them.... except the amount field which hopefully is set up to the summ of the amounts with the same categorization based on the incomeOptions...

the globalCalculation field is a global field set to number and the amount is a number field..

[BobWeaver]

The problem is that as soon as you create a new record, it goes at the very end of your file, and becomes the current record. So, you immediately jump past all the records that you wanted to loop through. The usual loop format is

Find

Sort

Go to Record [first]

Loop

  Do calculations here

  Go to Record [next , exit after last]

End loop

If you need to create a new record while in a loop then you need to remember what record you were on so you can get back to it like so:

Find

Sort

Go to Record [first]

Loop

  * Do some calculations here

  * About to create new record, so save current record number

  Set Field [gRecordNo, Status(CurrentRecordNumber)]

  New Record

  * Do stuff with new record here

  * Now omit it so you don't run into it at the end of your loop

  Omit record

  * Now jump back to the record you were on

  Go to record [gRecordNo, by field value]

  * Do more stuff with the current record here, if necessary

  * Finally, skip to next record and repeat

  Go to Record [next , exit after last]

End loop

[CobaltSky]

Hi toshog,

You'd already said that 'globalCalculation' and 'amount' are number fields, but I was asking about the 'incomeOptions' and 'letterHolder' fields.

[toshog]

The problem is that as soon as you create a new record, it goes at the very end of your file, and becomes the current record. So, you immediately jump past all the records that you wanted to loop through. The usual loop format is

sorry, but it doesn't put it at the end here...

ok. if the first and second records have A for incomeOptions, then the 3d

and 4th records have B for incomeOptions. this happens:

goes to record one. sets the letterHolder to A then the globalCalculation to 1000.

then goes to the next record and starts a loop like this: checks if the

incomeOptions = letterHolder, if it does (in this case it does) it's

supposed to sum the value of the amount field of the second record to the

globalCalculation value (which by now is 1000). this doesn't happened.

next: now the letterHolder is different then the 3d field's

incomeOptions so it creates a new record - right there, not at the end;

this one now is the 3d record - and sets the amount field of this record

to the (supposedly) the globalCalculation value (which should be the sum

of 1000 + the value of the amount field from the second record BUT it's

not - the globalCalculation is still = 1000) THIS IS THE PROBLEM. right

here.

after that it creates another record which now is record 4. and then goes

to the next record - sets the globalCalculation to the amount from the

(now) fifth record (past 3d record) and the letterHolder to B. and the

whole starts all over again.

so the result is like this:

record 1,2, then record 3 has only a value in the amount field and record

4 is empty, what was record 3 is now record 5. that's what i get when i

run the script. that's what i want. EXCEPT the globalCalculation field

doesn't "grow" with each consecutive record. it stays unchanged until the

incomeOptions field changes....

i'm not sure if this is very clear but that's what happanes....

i would've send you the file with the script, but i'm not sure the client will like the idea....

[toshog]

Ray,

the 'incomeOptions' is a text field BUT the values there are picked by radio button field with list options from a value list.

the letterHolder is a global field set to text.

[Ugo DI LUCA]

I figured out for the new record-next record as a bad structure, but is there another method without using omitting, using flaged when script is done for example.

If flag, go to next...

I have "quite" the same need, but at the end, I want the script to go to a sorted list in a layout with summary report. So I want all records to appear.

[Razumovsky]

Toshog-

I think you should clarify why you need to create records that show these subsummary amounts. From the info you have posted, CbaltSky's first response seems the best advice: it would be very simple to view the records in preview mode with a subsummary layout sorted by "incomeOptions" and a summary field. This would show you all the "A" options, then their total amount, then all the "B" options, then their totals etc.

The method you propose seems to have several drawbacks: creates redundant records with static "summary" data- once you change an amount, the summary record is inacurate. Also, it would be difficult to distinguish between a summary record and one that had not been completely filled in. There are probably several other drawbacks as well.

If you must, however, do it this way, I attached a sample file with a script that will do what you need using a InvoiceOption::InvoiceOption relationship to assist. It is a bit complicated, but can be done.

I still am curious to know why you feel you need it this way.

HTH

SummaryTest.zip

[toshog]

Toshog-

it would be very simple to view the records in preview mode with a subsummary layout sorted by "incomeOptions" and a summary field. This would show you all the "A" options, then their total amount, then all the "B" options, then their totals etc.

explain please...

that's waht i need.... exactly that. the point of all this is to generate printable reports layed out like this:

first all the records that have "A" as a incomeOption then get their total amounts and put it under them, then do the same thing for B,C, D ..... once it's all done the total of everything should be on the next line....

it al has to be on the same page/layout. this layout is only for printing when the layout is swiched all the "empty records" get deleted. everything back to normal...

[Razumovsky]

I dont mean to be short with you, but you should really read the help file entries on summary and subsummary reports. These are very useful features that will come up over and over again, and the help file has plenty of information on them. It is definitely worth doing some research on.

Remember that they do not display the subsummary fields when in browse mode, they will print the way they look in preview mode.

Also, the layout setup wizard will create one for you pretty easily.

-Cheers