Field Input Summary

[Zero Tolerence]

Ok, I have a question for you guys.

Lets say, I have a record with 20 fields. Each of those fields can have a number between 1-20.

If I do a Case Statment, and start at the highest value possible (20), and work my way down, here's what I'm getting at.

If the 20 fields all have a 20, then I want to do something,

If 19 fields have the number 20, I want to do something else,

And so on down the line, I realize this will be a huge function, because I will go through this with the numbers 1 - 20, I start with 20, because I want the highest value possible first.

My question is, is there a way a summary (Sort of) on all the fields in ONE record. So I can just get:

"There are 19 fields with the number 20"

Thanks for any help.

[Ender]

Do you only need to look at fields that have a 20? If this is the case, then try

Count20 (calculation, number result) = field1=20 + field2=20 +...+ field19=20 + field20=20

SomeCalc (calculation, text result) =

"There are " & Count20 & " fields with the number 20"

[comment]

1. What Ender said, except you need parentheses around each comparison:

(field1=20) + (field2=20) +...+ (field19=20) + (field20=20)

2. IMHO, in most cases the need to summarize fields in a record is an indication that the data should be in a single field across separate records.

[Zero Tolerence]

No I will need to look at fields that have a 19 all the way down to 1. However, If a field has 10 20's, and 9 19's, then I want it to return that I had 10 20's.

Whereas, if a field has 10 20's, and 10 1's, then i want to return that it had 10 20's, because 20 is the higher number. Which is where my case statment comes into effect. i will start with 20 numbers being the same, work my way down from 20-1, then I will start with 19 of the same number, following the exact same formula. until I hit 1 1's.

Think of it as a game, each time its your turn, you roll 20 dice that all have 20 sides. The winner is the person who

a) has the most of the same kind.

: If both players have 10 (or any number) of the same kind, the person with the highest number wins (10 20's beat 10 19's)

c) If no players have duplicate dice, then the person with the highest dice wins so 1 20 would beat 1 19.

Of course thats not what I'm using it for, but it should be a fairly good example to show you what I"m looking for.

[-Queue-]

This doesn't seem like it would be too difficult, if you use a related table to hold your number data. You can then create a value list of the related values and use a repeating field to summarize the count for each, another repeating field to determine which numbers have the highest frequency, and a final field to determine the largest number with the highest frequency and produce your text result.

See attached for sample. You might be able to trim this down. But it's a decent start.

TwentyFields.zip

[transpower]

Uh, -Queue-, your attachment calculates 4 fields with the number 19, whereas it's supposed to say 3 fields with the number 20. Perhaps you could elaborate on your method just a little.

[-Queue-]

Yes, that is correct. It should be the most of the same kind, not necessarily the highest number, unless there are two or more sets of numbers with the same frequency, in which case the larger/largest of the highest frequency numbers should be the result. So, 4 with 19 is accurate. If 20 also had 4, then 4 of 20 would be accurate.

cHighest should be changed to text in the attachment, and a more grammatical calc for it is

Let( M = Max(crepFreq);

"There " & Case( M > 1; "are "; "is " ) & M & " field" & Case( M > 1; "s" ) & " with the number " & Max(crepHigh) & "." )

[Oldfogey]

Queue,

That is ORSUM, or as some say, awesome!!

(I've always been on of the anti-repeating field people so have never really looked at the relevant functions. I could be a total convert if only FMI would drop in some really good stuff so we had array handling. So near and yet so far!!)

[Zero Tolerence]

THank you alot Queue, now another small question. I now have the ability to check what I was trying, I took the code you had, and changed it a bit until it fit my need. Now, in the same game, of rolling 20 20sided dice, lets say I want to award points if the dice are in order. Like if you have dice 1-6 you get 100 points (Something like that.)

I just need to be able to check to see if there are also 6 numbers that are in order.

Right now, the way I'm looking at it is to make each field a letter.

Let

a = field1

and so on and then doing something like this.

Case

a + 1 = b and b +1 = c and c+1 =d

and so on. Is there an easier way to do this, because like that, it would be a lot of code.

[-Queue-]

It seems like this part would be most easily accomplished via script, but I don't have the time to concoct one right now. Perhaps comment will chime in again with a brilliant solution.

[comment]

Must I? Ah well...

Just thinking out loud - a custom function might do something like this:

parameter = n

PatternCount ( ValueList ; n - 1 )

+

Case (

n > 1 and Position ( ValueList ; n - 1 ; 1 ; 1 ) ;

customFunction ( n - 1 )

)

Can't really concentrate on this properly right now, so it might be utter nonsense. I too think a script would be much faster. As it is, your solution really crawls on my computer.

Here is another alternative outlook on both issues.

GameOf20.fp7.zip

[Zero Tolerence]

I figured something out. It still runs pretty smoothly for me, so here is what I did.

Let(

a = num1

b = num2

c = num3

d = num4

e = num5

f = num6

g = num7

h = num8

i = num 9

j = num10

k = num11

l = num12

m = num13

n = num14

o = num15

p = num16

q = num17

r = num 18

s = num19

t = num20

u = If( a + 1 = b or a + 1 = c or a + 1 =d....so on;

If(a+2 = b or a + 2 = c or a + 2 = d ...so on;

If a +3 = b

and so on. It was a lot of code, but I could copy and paste it into notepad and change what needed changing, its working pretty good, still having some problems but I'm working on those right now.

[comment]

If you going at it like that (i.e. brute force calculation), then perhaps it would be more convenient to concatenate all results first, then do a PatternCount() or Position() on the concatenation - something like:

Let (

Results = "x" & num1 & "x" & num2 ...

)

Case(

(PatternCount( Results ; "x" & num1 + 1 ) > 0 ) and ..

Anyway, here's the custom function I was musing about earlier. It's actually quite fast.

GameOf20.fp7.zip

[-Queue-]

Nice job, comment! Could you elaborate on the isSeries, PrevBalanceLU, Series, and cCountSeries fields though? I'm not quite grasping their purpose on a cursory inspection.

[comment]

isSeries: Boolean - is there a result that equals this result - 1? If so, then this result is part of a series (in a sequence n, n+1, n+2 ... all the results except n will be tagged as isSeries);

Balance: PrevBalanceLU + isSeries

PrevBalanceLU: looks up the Balance from the previous member of the series.

These three build up the count of the series, somewhat like a bank account, where each isSeries is a deposit:

result      isSeries     PrevBalanceLU    Balance

n               0           0                0

n+1             1           0                1

n+2             1           1                2

n+3             1           2                3

So that Balance=3 means that n+3 is the last member of a series of 4.

Hence Series = Balance + 1.

The PrevBalanceLU is a cascading lookup: I am not sure what the exact rules are, but it works properly when the sort order is n, ascending.

Basically the whole process is an emulation of a finger count.

[-Queue-]

Okay, that makes sense. I thought the idea, though, was to determine if there was a series in sequential order, which that doesn't do. Perhaps I misunderstand the desire.

[comment]

It provides enough information to determine that (unless I misunderstand you completely). The rules are not completely clear, so I have not gone further.

The only thing the poster said was "if you have dice 1-6 you get 100 points". What if I have 1-7? Does that count as 2 series of 6? Or is there an extra bonus for series of >6? What if I have 2 discrete series of 6?

So I just marked each result with its ordinal in a series and sorted by that. It should be easy to change Series to

Case ( Balance = 5 ; "100 points")

for example - if that's the rule.

[Zero Tolerence]

Very good, still a bit over my head right now however. I used my nested If statments and it worked for what I wanted, although it was quite a bit of code.

The value would work like:

Whereas there can only be a sequence of 5 dice for points, the end of the sequence is what determines your score. So, for instance. 1-6 gives you 100 points, 2-7 gives you 200 points and so on, all the way up. And, I have figured a way to do this also. While my method is most assuredly not the smartest or the quickest way to do what I have done, I'm proud because I got it to work the way I wanted it to. Perhaps when I have finished with my product, I will submit it to Queue and comment for them to have a look.

[-Queue-]

Must the values be sequential or do you merely require that they be in order, ascending or descending, regardless of how many values may be in between each of the sequence values?

[Zero Tolerence]

Sorry for that confusion, to get the points for 1-6 you would have to have the following numbers Anywhere in the 20 numbers you have.

1,2,3,4,5,6

So, your 20 numbers could be: 1,4,5,6,2,8,9,13,12,3,...

and you still would get the points.

[-Queue-]

Okay, so if you have 1 - 7 anywhere in the set, do you get 300 points? Elaborate on how the scale works.

[Zero Tolerence]

You only get points for 6 numbers. 1-6 anywhere in the record (In order or not) would net you 100 pts. Whereas 2-7 would net you 200 and on up.

[-Queue-]

Okay, here's a crazy idea.

Raise each unique result to a power of 2. Create a value list based on the result. Add all the values together by substituting

GameOf20.zip

[-Queue-]

And here's a much simpler version that doesn't use a Custom Function or an additional value list, but it's not as much fun. The binary string is simply a calc of PatternCount(

GameOf20v2.zip

[comment]

You could also create the string by summing 10^(result - 1), avoiding the conversion to binary. You would have to break it into two pieces, though, since it is beyond the bit limit of the system.

[-Queue-]

Good point!

[-Queue-]

For fun I ran two tests of 10000 randomizations each. Here are the results:

                Test One        Test Two

Score     Frequency     Frequency

   0             6487               6404

 100            190                  201

 200            195                  189

 300            211                  203

 400            188                  223

 500            189                  219

 600            193                  220

 700            194                  256

 800            235                  195

 900            228                  228

1000           240                  251

1100           230                  233

1200           236                  216

1300           239                  234

1400           238                  216

1500           507                  512

It makes sense why 1500 is more likely than the rest, since the highest group of six wins. But does it really make sense that it is 200% more likely than 1400 when 1400 appears less often than 1300?

Is there something I'm missing or are the random numbers very non-random?

[comment]

I don't think there's anything wrong with the random numbers. The game is not level.

The thing is, when you have a sequence longer than 6, the higher number "hijacks" the bonus and skews the stats. So you're not counting ALL the instances where the player had, for example, a series of 1 to 6. You're only counting those instances where the player had a series of 1 to 6 AND didn't have a 7.

The sequence 15-20 is unique in this aspect: there's no 21 to come and "hijack" part of the occurences.

I have constructed a mini-model of the game, using only three numbers, and awarding prizes for a sequence of two. If you look at the attached truth table, it tells the whole story.

[comment]

I shoud have said "you're only counting those instances where the player had a series of 1 to 6, AND there isn't another series of 6 up the road".

[Zero Tolerence]

Very nice both of you. I just did a let statment on it through a rough calc, and then compared the results. So in three fields it would look like this.

Let([

a = 1st Dice

b = 2nd Dice

c = 3rd Dice

d = If (a + 1 = b or a + 1 = c;

If(a + 2 = b or a + 2 = c;

That would be repeated for a - c ( b+ 1 = a / c +1 = a.

Then I did

g = If (d ? ""; d + 2; "');

h = If (e ? ""; e + 2; "");

i = If (f ? ""; f + 2; "")

j = Case( g > h and g > i; g; h > g and h > i; h; i > g and i > h; i;)

Which gave me the highest sequence as J. Then to get the points I would just do.

k = j * 100;

then I would do my checking for 5 19's and so on. and add my results.