# Summary or Calculation field?

## Recommended Posts

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.

Greg

##### Share on other sites

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 by Guest

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

When is this happening?? Escapes my memory!

--sd

##### Share on other sites

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 by Guest

##### Share on other sites

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

##### Share on other sites

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 by Guest

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

--sd

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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?

##### Share on other sites

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 by Guest

##### Share on other sites

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.

##### Share on other sites

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.

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

1 member has viewed this topic:
Matt Cudmore
×
×
• Create New...