Jump to content
Sign in to follow this  
Greg Hains

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.

Any ideas please?

Greg

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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
Adding comment

Share this post


Link to post
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 this post


Link to post
Share on other sites

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

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

--sd

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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...

Multiple Surveys

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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