Summary or Calculation field?

[Greg Hains]

Hi.

I am trying to achieve something that has been eluding me for hours.

I have a database of jobs, and within each job is a portal to a table for timesheets. One of the fields in timesheets is a Yes/No field that records if a time is billable or not.

Below this portal I have setup a total that sums all hours in the timesheet for that job. This works fine.

What I am trying to do is sum all the VALUES of the timesheet entries marked Yes, and another for No (which would in fact then add up to the total of all hours in the timesheets).

I have tried all manner of trying to setup a field that totals all the values in another field in that portal given the Yes or No.

Any ideas please?

Greg

[RodSierra]

Well one way is:

Make your yes no field a number field 0 for no and 1 for yes. ( You can display the yes no text by using the format number field)

Then do an aggregate, one for yes that is the sum of the field, and the other for no that is the count of the field minus the sum.

Edited April 18, 2007 by Guest

[Greg Hains]

Hi Rod.

Sorry, Im not quite with you. Here is a sample table with two fields of what I mean.

- duration (a number)

- billable (yes/no)

At the bottom of the portal I want two totals - one of the sum of all duration records where the billable is yes, one of the sum of all duration records where the billable is no.

Its highly likely you answered this correctly, but I couldnt make sense of it sorry. I can tally the Yes fields and No fields, but not sum the duration field based on the Yes/No answer.

Thanks.

Greg

[RodSierra]

Greg Ok then I'd do two calc fields, or if you wish autoenter fields:

Billable:

case(billable = yes ; duration ; "" )

NonBillable:

case(billable = no ; duration ; "" )

Then do the aggregate totals on these fields.

[Søren Dyhr]

Isn't the best solution, to facilitate the solution with a TO for each purpose??

Here could a point be how long are each index, the foreign keys "wear" ...the shorter version is the less space consuming, since primary key only holds value in the present record ...the trade off is that primary keys need to be indexed in order to make a GTRR(FS) after establishing a found set in childrecords, but still is the indexing although redundant pretty short (...one)

I include both methods in the attached file....

--sd

Nyt_arkiv.zip

[Søren Dyhr]

Its highly likely you answered this correctly, but I couldnt make sense of it sorry. I can tally the Yes fields and No fields, but not sum the duration field based on the Yes/No answer

Don't worry I can't either, further more reflects the answer some idiosyncrasies in the syntax, there quite isn't catered for...

--sd

[RodSierra]

try to help, soren and I get this type of non english cryptic response, well I for one have had it with your thread busting posts that mean no sense in the english language.....signing off from this web site as I have no time for this type of nonsense, I've seen posts busted by you for too long with poor english that resulted in a lot of I'm not understanding what your saying...its clear this site welcomes you but I do not, so finding somewhere else to go.

[Inky Phil]

It's a funny old world isn't it?

Rod spits his dummy out of the pram because he thinks that Soren, to whom English is a second language is not clear enough in his answers when in fact Greg can't understand Rod, the native English speaker.

It also just occurred to me that Soren with his 3035 posts probably has a lower 'misunderstood' percentage than Rod with his 47 posts.

Like I said, It's a funny old world.....

[Greg Hains]

Thanks for this. 'Case' didnt work as well as an 'If' but it sorted it out OK.

Whilst I have you, as the totals were outside the portal (and hence in another table), is it possible to setup a summary or calc field in that outside table that can sum the fields from the portal? It seems that summary fields only let me use fields from that table.

Not urgent.

Once again, thanks for your help.

Greg

[Søren Dyhr]

'Case' didnt work as well as an 'If'

When is this happening?? Escapes my memory!

--sd

[Darwin]

This is exactly one of the things I need. Can you show me an example of how to make the yes/no field into a number? I assume I will have to make a calculation field.

Maybe you can help me out further. I might be making things more complicated than it is.

I have a database that has multiple yes no fields. I need to turn yes's into numbers. Not always 1 for yes. Is that possible? Some yes's are weighed more than others because in the end I need a score of how many yes answers I have and I will eventually need a score in a percentage. Whadda ya think?

Edited April 18, 2007 by Guest

[Søren Dyhr]

Perhaps you should consider turning a number field into exhibiting boolean behaviour in your layout:

In Layoutmode select Format>Number>Format as boolean

An other option is a calc'field:

PatternCount(thefield;"Yes")

--sd

[Darwin]

Ack, Soren, that's a little over my head. Here's what I have. I have two fields:

Greetings - Yes No N/A

Uses Clients Name Yes No N/A

Two Summary Fields

Greeting Summary (Count of Greetings Field)

Uses Client's Name Summary (Count of Uses Clients Field)

Right now my yes's will show as 1 in the summary fields. I'd like to change that value. 5 instead of 1 for example. Then have a calculation field that shows the total of all the summary fields in a percentage. As of right now the Summary feilds do not work properly. They show a 1 even when it's a "No" answer.

Edited April 18, 2007 by Guest
Adding comment

[Darwin]

I had posted in the middle of another post so I thought I'd just start a new topic.

Here's what I have. I have five fields:

Greetings - Yes No N/A

Uses Clients Name Yes No N/A

Two Summary Fields

Greeting Summary (Count of Greetings Field)

Uses Client's Name Summary (Count of Uses Clients Field)

A Calculation Field

Totals (Totals of the two summary fields)

Right now my yes's will show as 1 in the summary fields. But not all the time, as in it'll show a 1 even if it was a "No". I may have the concept of summary fields wrong. I'd like to change that value. 5 for "Yes" instead of 1 for example. Then the calculation field will show the total of all the summary fields.

[Søren Dyhr]

Alright boolean fields have only two values, so radio buttons is a more obvious metaphor here. I've made you a template...

--sd

Untitled.zip

[David Jondreau]

You might get a more helpful answer if you posted the whole problem. What are you trying to accomplish exactly?

[Søren Dyhr]

See what a limbo, to be between cross posting and hijacking!

Meanwhile have I made you a template in the previous thread.

--sd

[Darwin]

Oh wow, thank you!

Ok, I think I understand the ratio's. The ratios's of Yes's versus no's.

What I need is a field with a calculation of what percent of the fields are yes's.

[Darwin]

You're right, sorry about that guys.

What I am trying to accomplish is a yes, no, n/a question worksheet. Then all I need is a total of all the yes answers. I'll need a field for what percentage were yes answers.

Further along the line I'd like the questions to have different weights. But anyway.

I've attached Soren's example. I tried to add a total field but couldn't get it right.

Untitled.fp7.zip

[mr_vodka]

I have a database that has multiple yes no fields. I need to turn yes's into numbers. Not always 1 for yes. Is that possible? Some yes's are weighed more than others because in the end I need a score of how many yes answers I have and I will eventually need a score in a percentage. Whadda ya think?

I think that this sample may of use to you...

Multiple Surveys

[David Jondreau]

The total is already there. It's the field named firstSummary. The percentage of yes answers is Ratio1.

[Darwin]

Ratio1 only calculates the Greetings field. Ideally I would need Ratio1 to calculate both.

[Søren Dyhr]

Indeed one more summary field and a PatternCount( is required, this time counting either No or N/A! The last is then obviously the diff between the two others.

--sd

[Darwin]

Indeed one more summary field and a PatternCount( is required, this time counting either No or N/A! The last is then obviously the diff between the two others.

--sd

Wish me luck, I will try yo make the summary feild and the Patterncount for the No's. I think we're getting closer?

[Darwin]

John, thank you for the wonderful sample. It;s too complicated for me to take apart but it looks like what I need mine to look like. I thought my issue would have been simpler than it is but it sure is getting complicated. But, I just love all the help I am getting. Thanks for everyone in advance.

Edited April 19, 2007 by Guest

[Darwin]

Ok, I figured it out.

(GreatingsToNum + UcnToNum) / 2

Much thanks to you folks for getting me this far. Now the interesting part is when I need to make the questions weigh more. As in some questions should score higher.

[David Jondreau]

You need to create a field that assigns a value to each question (Weight). Then you need a field that = Weight*PatternCount(YesNo; "Yes"). Then you need to redo your summary fields.