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

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

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

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.

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)

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

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

Attached an example.

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

TestNos.zip

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

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

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

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

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.

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.

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.

Jesus Peter,

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

Both of you are publishers.

If so, welcome back.

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

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

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

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

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

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

Hi,

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

Actually, seems to work without Refresh

TestNos6.zip

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

TestNos7.zip

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

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

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

neat.

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

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

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

Pilcrowcounting in ValuelistItems is straight forward just:

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

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

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

--sd

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

Joseph

Nope, you have to define a new list utilizing the relation:

1) define valuelist

2) use values from field (press specify)

3) choose table and field

4) CHOOSE "Include only related values" including the from....

It would probably take some time to grasp if you havn't done it before ...The easiest way to check is to make a unstored calc' with ValuelistItem( before you let it be part of the inner mechanics of the solution.

--sd

Joseph,

Take a look at the last sampler I submitted as it should answer the case with very small adjustments. I preferred Soren's for the initial need of a Yes/No flag, but if in fact you need to do some dynamic comparisons on the index, having the Value List deployed is the solution.

The calc used to determine wether there's one or many brokers is actually evaluationg the number of pillcrows. Just change it so that you get the number instead of the "Only one/More han one" result.

You guys are so above me in FM is Funny... I like learning this stuff but I does get a little confusing when you have so many relationships already...

I will study the sample that you gave me... Thank you..

Joseph

Ok I give... I am just not that good at this yet.. If it is no trouble can you show me an example... All I want to do is count (Number for the answer) how many times in a portal column a new word shows up...

ex:

Bob

Bob

Jack

would be 2

ex:

Bob

Bob

Bob

would be 1

Thank you

Right I make the alterations to Ugos latest..... Please download!

brokerscounted.zip

Ok, fter pulling my hair out... I got it to work with my data base... I really thank you for showing me this...

If you have the time can you answer these for me:

1. What are the Parigraph marks; and what do they mean?

2. Why does it say 1 even if ther is no entry in a cell... can it say 0 or stay blank if there is no entry

Thank you

Joseph

Why does it say 1 even if ther is no entry in a cell... can it say 0 or stay blank if there is no entry

Are you ready for the next trick?? Draw a new portal around the calcfield showing the same relation as the portal. But here is it just one row and the lines the portal consists of is changed to none width. This is known as the visibility trick. The field in the portal isn't from the related table but is the calc with ValueListItem( ...

http://www.filemakerpros.com/Visible.sit

http://www.filemakerpros.com/Visible.zip

Why do I make such a silly thing? Because debugging as well as recalculations are faster than building the calculation that otherwise would dependant on the number fo rows in the portal.

If we should have done it that way would the calc have been:

Case(Count (RecordsinPortalFile PORTAL::LogInID;PatternCount(ValueListItems(Get(FileName);"RelatedBrokers")& "

I know why you are similing in your picture... It's b/c you are really good a FM. Ok, I think I am ready for your next trick, but I have to ask something first before I do a lot of thinking to follow what you are saying...

I wanted to use the calc field with an report... meaning when you print a report it will show how many there are for that field (Difference only). So if I make a new portal and then make it invisable does that mean it will not show up on the report as 1 when there are really zero?

Joseph

So if I make a new portal and then make it invisable does that mean it will not show up on the report as 1 when there are really zero?

No you're correct here, the Case( is what's required here! And it might even have to be put inside an Evaluate( to provide correct breakings??

--sd

Ok, Thank you I will study this.... Thank you looks like I have a long weekend...

Joseph