Repeating values and calcs

[crazybake]

I have a popup list which lists cities. The user selects the city and hists the "go" button. The user then is given a list of stats (from summary fields..i.e. percentage of females in that city as a percent of total individuals in the database)...

When I do this one at a time, it works great. The user then has the choice to compose a letter etc....

However, I am trying to create an "info" layout in which the user will see a repeating field with all of the cities on the left and the corresponding calc values on the right. Is it possible to do this without a script...

Version: v6.x

Platform: Mac OS X Panther

[Ender]

I can think of one way, but it involves scripting to put the data together:

Use a getsummary(Count of Records, <Field>) calc to get breakdown counts for each city. Then sort the records by City and <Field>. This will yield records in order like this:

City Gender Count

Austin F 1

Austin M 3

Austin M 3

Austin M 3

Minneapolis F 2

Minneapolis F 2

Minneapolis M 5

Minneapolis M 5

Minneapolis M 5

Minneapolis M 5

Minneapolis M 5

New York F 2

New York F 2

New York M 1

That's step one. And you will need to define a getsummary() calc field for each type of breakdown... Hmm--maybe you could use getsummary(Count of Records, getfield(MyField)), where MyField is a global text field that gets set with the corresponding field name for your breakdown...Don't know if this would work.

Anyways, next create a looping script to go from the first record to the last record to extract the getsummary counts, putting the data in a GLOBAL text field, portal, or (God forbid) a repeating field. Use a global Current Record field to track which record you need to look at:

Sort [Restore, No Dialog]

Go to Record/Request/Page [First]

Set Field ["Current Record","1"]

Set Field ["Output", City & " " & Gender & " " & Count by Gender"]

Loop

Set Field ["Current Record","Current Record + Count by Gender"]

Go to Record/Request/Page ["Current Record"]

Exit Loop If ["Current Record > Status(CurrentFoundCount)"]

Set Field ["Output","Output & "<Paragraph>" & City & " " & Gender & " " & Count by Gender"]

End Loop

The resulting sample data would look like this:

Austin F 1

Austin M 3

Minneapolis F 2

Minneapolis M 5

New York F 2

New York M 1

You can clean it up more by putting the data into separate fields or formatting it differently, but it should work. I use this method to move summary data into a charting plug-in. Works great.

--Mike

[Ugo DI LUCA]

crazybake said:

The user then is given a list of stats (from summary fields..i.e. percentage of females in that city as a percent of total individuals in the database)...

If all datas that should show in the repeating fields are of this type, then you don't need any "Summary fields".

And you wouldn't need any script either, at first glance.

Version: v6.x

Platform: Mac OS 9

[Ugo DI LUCA]

Attached is a test

Version: v6.x

Platform: Mac OS 9

RepAndStats.zip

[-Queue-]

Status(CurrentRepetitionNumber) doesn't work on 5.5. Does it on 6? It's a sweet way to get an Repeating Index in 7...

[Ugo DI LUCA]

Corrected version.

Status(CurrentRepetitionNumber) works in 5.5 JT. This tip was made with 5.5 actually, but there was a buggy behaviour due to bad lookup settings.

Version: v6.x

Platform: Mac OS 9

RepAndStats.zip

[-Queue-]

In scripts, yes; in calcs, no, at least not on Windows. The only thing it gives is a zero in the first repetition and nothing else, no matter whether it's unstored or not.

[-Queue-]

Oh, I see what you're doing. Yes, it works as a single value determined once a field is entered, but not as a repeating calc of itself like 7 does, i.e.

1

2

3

4

5

for a repeating field with 5 reps.

[Ugo DI LUCA]

In fact, even when corrected, the results are pretty inconsistent. There's a need for a second trigger in some place to make sure the current Rep number stays live for the lookup to happen for all fields.

I'll try to correct it again, but it's becoming rather tedious, and I imagine once set up, one would move to relationships as quick.

I have no idea what 7 is doing though.

Version: v6.x

Platform: Mac OS 9

[-Queue-]

Yes, I've noticed this problem after selecting the same city on multiple repetitions.

I'm curious why cRow isn't just Case(Last(tCity), Status(CurrentRepetitionNumber), Status(CurrentRepetitionNumber)) versus Abs(TextToNum(RightWords(Case(Last(tCity), Status(CurrentTime) & "-" & Status(CurrentRepetitionNumber), Status(CurrentTime) & "-" & Status(CurrentRepetitionNumber)), 1))).

Care to enlighten me?

[-Queue-]

I'd also add a Freeze Window before the Clear loop and switch the Go to Next Field and Set Field [""] so the first city is nullified. But that's all interfacial bs.

[-Queue-]

And deselect "Don't copy contents if empty" on all the lookups so that old data isn't left if the new selection has no related data.

[Ugo DI LUCA]

Hi,

Here's the revisited version, with the 2nd trigger involved.

All steps are detailled.

JT, you know I like to complicate stuff, so yes the Case(Last(tCity),Status(CurrentRepetitionNumber),Status(CurrentRepetitionNumber)) is easier.

It was derived from another file where I had to keep both the time and rep number.

What do you think. You'd like to have such an Industry in your files ?

Version: v6.x

Platform: Mac OS 9

RepAndStats.zip

[BobWeaver]

Queue,

Status(CurrentRepetitionNumber) only works in scripts. It doesn't work in field calculation formulae. You have to do a workaround by making a global repeating field and fill it with the sequence of index numbers and reference that in the formula.

[Ugo DI LUCA]

Hi,

My apologizes, the related file was missing in the previous attachment.

Here it is again (I should delete all the others.. )

Now, about the Status(CurrentRepetitionNumber), as for all other Status Functions actually, you may use them as an instant trigger when combined into some kind of formulae, as described by Queue.

I'd have to dig my hardrive, but I did once use this technique to have dynamic relationships established...

Version: v6.x

Platform: Mac OS 9

RepAndStats.zip

[-Queue-]

Um yeah, Bob. That's what I said. And that's what I've been doing. I was just curious if 6 had 7's feature where this works nicely and does away with the need for a global and a lookup, if the field must be indexed. It is nice to know that it does work for calculations resulting in a single value when a repetition is entered, though, as Ugo's example demonstrates.

Now, if 7 only had every other version's ability to work with related repetitions, it might be worth upgrading. I'm hoping they release a fix for it since it is a serious 'bug' and most definitely not a 'feature'. Eh, Ugo?

Version: v5.x

Platform: Windows 2000

[Ugo DI LUCA]

It sure is a bug, and I too hope they will fix it.

Version: v6.x

Platform: Mac OS 9

[BobWeaver]

Sorry Queue, I missed your earlier post.

I believe FMI's official word was that some of these status functions were for script use, and were never intended to work in calculated fields. I don't know if that policy has changed with version 7.

[-Queue-]

No prob, Bob.

Check out Get( CalculationRepetitionNumber ). They've added this to work for calculations. So an 'index' field can simply be a calculation of Get(CalculationRepetitionNumber), with no need for a global or lookup. Pretty sweet!

[BobWeaver]

Yeah, it's about time they had this feature. It will be nice to see what else is in FM7, if I ever get caught up in my work, to find time to download the demo copy.