Greg Hains Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
RodSierra Posted April 18, 2007 Share Posted April 18, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Greg Hains Posted April 18, 2007 Author Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
RodSierra Posted April 18, 2007 Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
RodSierra Posted April 18, 2007 Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
Inky Phil Posted April 18, 2007 Share Posted April 18, 2007 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..... Link to comment Share on other sites More sharing options...
Greg Hains Posted April 18, 2007 Author Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 'Case' didnt work as well as an 'If' When is this happening?? Escapes my memory! --sd Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
David Jondreau Posted April 18, 2007 Share Posted April 18, 2007 You might get a more helpful answer if you posted the whole problem. What are you trying to accomplish exactly? Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 See what a limbo, to be between cross posting and hijacking! Meanwhile have I made you a template in the previous thread. --sd Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 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. Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
David Jondreau Posted April 18, 2007 Share Posted April 18, 2007 The total is already there. It's the field named firstSummary. The percentage of yes answers is Ratio1. Link to comment Share on other sites More sharing options...
Darwin Posted April 18, 2007 Share Posted April 18, 2007 Ratio1 only calculates the Greetings field. Ideally I would need Ratio1 to calculate both. Link to comment Share on other sites More sharing options...
Søren Dyhr Posted April 18, 2007 Share Posted April 18, 2007 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 Link to comment Share on other sites More sharing options...
Darwin Posted April 19, 2007 Share Posted April 19, 2007 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? Link to comment Share on other sites More sharing options...
Darwin Posted April 19, 2007 Share Posted April 19, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Darwin Posted April 19, 2007 Share Posted April 19, 2007 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. Link to comment Share on other sites More sharing options...
David Jondreau Posted April 21, 2007 Share Posted April 21, 2007 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6216 days old. Please don't post here. Open a new topic instead.
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 accountSign in
Already have an account? Sign in here.
Sign In Now