Jump to content

Field Input Summary

This topic is 6601 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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"

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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) & "." )

Link to comment
Share on other sites


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!!)


Link to comment
Share on other sites

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.


a = field1

and so on and then doing something like this.


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.

Link to comment
Share on other sites

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. smile.gif

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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


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.

Link to comment
Share on other sites

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 ...



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

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


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.


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.

Link to comment
Share on other sites

This topic is 6601 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.