Using PatternCount on a portal

[Joseph31]

Hello Everyone,

I need to do a simple count on one field in my portal. I need to count only the Yess.

I have tried using

PatternCount (File_Login_SubSheet_Brokers::Entry_FileMaker ; "Yes" )

This seems to work but it seems tobe counting the first row of my portal.... How do I get it to count all of lines in my portal if they contain Yess.

Help?

Joseph

[Peter1]

What works for me is I make the yes value in a checkbox actually a 1. This is then very easy to Sum. ie:

Sum ( File_Login_SubSheet_Brokers::Entry_FileMaker) where Entry_FileMaker is 1 if yes (or 0 if No)

Peter

[Joseph31]

That makes sense, Well that adds some other confusion for me...

This is the reason I wanted to do the pattern count

In the portal i have raido buttons in the last field.... Field Name "Entry_FileMaker"... this field can be either Yes or No -- Any user can change the status when they have completed the entry into another system...

So what I wanted to do is if all of the answers are "Yes" in this field a main field that is not user changeable would turn auto to "Yes" if one field was or is left "No" then the main field would say "No"

Any ideas

Joseph

[Ugo DI LUCA]

Then, just test for a valid relationship to the "yes" value or Choose(SumCalc=0;"No";"Yes").

Also, if this was on a FM6 file Yes and No wouldn't even have to be converted to numbers as they evaluate as 0 and 1 automatically.

[Peter1]

Not only would I Sum the 1 which represents the yes entry, I would also Sum a fixed value of 1 - ie: return the total number of related records in the portal. When the Sum of Yes1 = the Sum of Fixed1, then all values have been entered as "Yes".

Create a calculation that simply returns the number "1". Call this field "Fixed1" (I always have a field like this in every table - I actually just call mine "one"). This is very useful to get the Number of Related Records with the simple calculation:

Sum(Related::Fixed1)

[Ugo DI LUCA]

Peter,

If really, you have this field "one" which we would have called a constant in prior version, then you can just check for a valid relationship to this 1 value.

If ever there's a match, then "Yes", otherwise, "No".

Or would it be the opposite..

[Peter1]

Isn't the idea to know if all values in the portal are entered as "Yes" ? Surely one would need to compare the Sum of Related Records to the Sum of entries with "Yes1". If the values are the same, then all entries are "Yes".

Peter

[Ugo DI LUCA]

Attached an example.

If there's a No value in the related records, turn to "No" immediately.

TestNos.zip

[Ugo DI LUCA]

Isn't the idea to know if all values in the portal are entered as "Yes" ? Surely one would need to compare the Sum of Related Records to the Sum of entries with "Yes1". If the values are the same, then all entries are "Yes".

Peter

Not necessarily. You just need to know if there's even one No left, and it would be quicker to test for a valid relationship on the No match than a count of all yes's

My 2 cents

[Peter1]

It seems as if your example is only NO when the first line is NO. I have attached how I would have done it.

However, your logic of testing for a valid relationship against the No is excellent. I will remember that in future.

Peter

TestNos2.zip

[Ugo DI LUCA]

oops..

Here's a corrected version as well as one with your "one" field as a "1/0" value intead of pure text Yes/No.

TestingNos.zip

[Joseph31]

Thank you for the example

I do not think I did a good job of explaning my problem, the example is close but it only turn No when you go to that record.

My setup:

Login_Page -- this is where I have a portal

also a field named Complete (yes or No)

Name -- Persons name

my portal which can have many entries linked to that name... each row can have a yes or no... what I need is a calc that will say look at each row of this portal (only this assocation to the name) and tell if if all rows have yes if not say no

this answer goes into the complete (yes or No) field.

can you help me alittle more on this?

Joseph

[Peter1]

I think we are doing this - at least in principle. Have a closer look at the files - or maybe we/I have lost the plot. It can happen!

Cheers, Peter.

[Ugo DI LUCA]

I think either Peter's sampler and my 2 new ones would do the job.

The only difference is that yours would point to a specific relationship while ours, for matter of simplicity was testing all database through a cartesian join.

[Ugo DI LUCA]

Pete,

Our clocks are in synch, except I'd need to run to sleep now. If you happen to know Peter Fenner (he was posting on the Forums a whil back) in Cape Town, give him a "hello" for me.

[Ugo DI LUCA]

Jesus Peter,

Are you the Peter I know with a new nick name ?

Both of you are publishers.

If so, welcome back.

[Peter1]

Hi Ugo

Yes the same Peter. It is nice to be back. I hope you are well and congratulations on Carpal Tunnel status. I am sure we will get to chat a lot more. Sorry for staying away so long - life gets in the way sometimes!

I have attached a file more in keeping with what I believe is what Joseph is trying to achieve - still based on my constant of 1 structure. Of course either way can work. I have fixed mine up to, cause you only need the relationship that is showing the portal records. I lost the plot a bit.

Take care all, Peter.

TestNos3.zip

[Søren Dyhr]

Another way is to count pilcrows in ValueListItems( over the dynamic valuelist of answers in the portal. If a pilcrow exists is the result mixed ...only thing is ValuelistItems( are avoidant in its updating!!!

But it solves the doubleposting variation of this question.

--sd

[Joseph31]

Ok Guys all of your help work, What really helped was the attachment that "Peter1" gave me, now that i look at it I under stand Yes or No calc.

I have one last question sort of pertaining to this kind of thing:

Same portal I have a field called "Broker" I would like some way to know if all of the brokers in this field ar the same or different -- this ones a little harder b/c there can be a unlimited amount of choices to go in this list.

Example:

"Broker"

1. Bob

2. Bob

3. Bob

This would be Same or Yes

"Broker"

1. Bob

2. Bob

3. Jack

This would be Not or NO

Remember that there can be any name in this field.

Thank you again

Joseph

[Peter1]

I have attached a file of how I would do it.

I created a calculation which simply shows the name of the Broker in the related portal - obviously this will only return the first Broker in the portal. I then Related this calc field to Broker and did a sum of the constant 1. Comparing this to the sum of total records in portal, produces the result you want.

However, there may be a way to more easily know when only one value is in a particular related field. I don't know how though.

In my method, the result will only show once the records are committed - ie: when you click out of the portal.

Cheers, Peter.

TestNos4.zip

[Peter1]

NB: The above post does not work with more than 1 login screen, I have adapted the file to include the login serial nr in the relationship to broker. See attached.

Peter

TestNos5.zip

[Joseph31]

Wow, That gets really confusing along with the other fields used in the Yes No equation. If you think of like maybe an eaiser way like PatternCount (File_Login_SubSheet_Brokers::Entry_FileMaker ; "Yes" ) let me know ok...

Thank you for all of you help

Joseph

[Ugo DI LUCA]

Hi,

You could have only 2 Tables and a test for the "

[Ugo DI LUCA]

Actually, seems to work without Refresh

TestNos6.zip

[Ugo DI LUCA]

And here's one with less fields in the related file and without the Sums..

TestNos7.zip

[Søren Dyhr]

It has bothered me slightly that the calc's were so value dependant, furher more is the use of a constant not required by the new stacking of relationships! My wish is to make a general model.

Why is the use of constants a problem ...they take up good storage while being both stored and indexed opposed to unstored values that only is brought to live when needed.

--sd

General.zip

[Joseph31]

Could you explane this better to me, B/C your half right.. I do get my List of brokers from a value list. About 15 names long... please explan how this equation works... if you have the time:

Thank you

[Søren Dyhr]

Eventhough your value list consist of 15 names is it whats in the portal that counts due to the dynamic aspects of the valuelists def.

The issue with valuelists you apparently miss is several occurences of the same value in a return/pilcrow delimited field ...is boiled down to just one occurence. If only one broker is chosen will the the valuelist be one single word without a return/pilcrow ...make sence now.

--sd

[Ugo DI LUCA]

neat.

I don't like ValueListItems( ) to be permanent field of a Parent record that much myself, specially with long lists.

[Søren Dyhr]

I don't like ValueListItems( ) to be permanent field of a Parent record that much myself, specially with long lists.

Neither did I, hence my upload "General.zip" ...the new relational features allows us to be easy on the calcfields - at least makes it us think in abstractions first and then denomalize accordingly with fixes.

--sd

[Joseph31]

Ok So now I have that equation worked out.... I just have to keep applying it so I can get a better handle on this.

Thank you for your help guys.

Oh, can this be applied to give a number ie: Count the differences in a list only

"Broker"

1. Bob

2. Bob

3. Jack

Returns value of 2

"Broker"

1. Bob

2. Bob

3. Bob

Returns value of 1

Any ideas?

Joseph

[Søren Dyhr]

Pilcrowcounting in ValuelistItems is straight forward just:

PatternCount ( ValueListItems ( Get ( FileName ) ; "theList" ) & "

[Joseph31]

I have thought alot about this equation... I have read the help menu and I am just not sure how this equation works....

What is the list -- is it the field name of the what I want to count?

When I type in the name of the value list of brokers all it does is count the list gives me 26... what I need is it to count the differences only... as in my example above

Thank you

Joseph

[Søren Dyhr]

Wrong list it should have been the dynamic one showing only the related data. If it misses make it!

--sd

[Joseph31]

I have a clone of the broker table is that what you mean by a dynamic list showing the related records?

Joseph