Jump to content
DreadDamsel

Counting the number of checked boxes in portal rows

Recommended Posts

DreadDamsel    0

Hi, I have a bit of a dilemma that I've tried a number of solutions to sort out, to no avail.

I have a portal that lists the participants in a class. In each row is a checkbox that can be used to indicate whether that participant has withdrawn from the class.

I've used a summary field to count the number of checked boxes and have a field that subtracts that number from the total of records in the portal - easy peasy.

Where it gets tricky is that, one of the fields relates to the number of children associated with a participant (a mum may have 3 kids in the class if it's one of the playgroups). Now I need to know how many kiddies there are in the class. I have a field that totals the number of children in total and that works fine, but...

... and here's my problem -

where a parent has withdrawn (and the box is checked), I need to subtract the number of children associated with that parent from the total number of children).

The checkbox is a value list with a single value, 1.

All the solutions I've tried so far are fine unless it pertains to portal rows - and I can't figure it out for the life of me.

Can anyone help me out?

Share this post


Link to post
Share on other sites
Posted (edited)

A sample file would be helpful, but if I understand you, I think checkboxes are not the way to go.  I think for each row you should have a field for participant count.  

So a portal row with a parent would have that field as 1. A portal row with a parent and 2 kids would have 3. Counting those would be easy with Execute SQL or a standard sub summary report

Edited by Steve Martino

Share this post


Link to post
Share on other sites
DreadDamsel    0

Thanks Steve,

classes aren't confined to childcare only, most of them are single person participants, and that count is easy to ascertain correctly.

Childcare however, is just the number of children. The parents aren't there. I already have a field that is a calculation that counts the total listed children associated with a parent (in the parent's contact details elsewhere).

We have to keep track of whether the participant is active or inactive because they may not come to all the sessions. If they stop coming, they're marked as Inactive (the checkbox), and the class number reflects only active numbers.

The inactive box is also linked to their enrolment history, so we can see what people have enrolled in in the past. Once a class is completed, all participants are marked as inactive as that activity has ended.

All I need to do is get a total of the number of children there are where the parent has been marked as inactive in that class. Once I get that, it's a simple matter of subtracting it from the total number of children (which I've already got the calculation for).

Share this post


Link to post
Share on other sites
comment    1,381

There are several ways this could be accomplished. A simple one would be to add a calculation field to the Participants table, along the lines of =

If ( not Inactive ; NumberOfChildren )

then change your existing calculation to sum this field instead of the NumberOfChildren field.

 

4 hours ago, DreadDamsel said:

I've used a summary field to count the number of checked boxes and have a field that subtracts that number from the total of records in the portal - easy peasy.

I don't see why you need the summary field; you could do simply:

Count ( Participants::ClassID ) - Count ( Participants::Inactive ) 

 

  • Like 1

Share this post


Link to post
Share on other sites
LaRetta    479
Posted (edited)
7 minutes ago, comment said:

I don't see why you need the summary field; you could do simply:


Count ( Participants::ClassID ) - Count ( Participants::Inactive ) 

 

I agree, of course, but make sure that the Participants::Inactive field does not contain zeros because they would be counted. Using Sum() might be better for that field as:

Count ( Participants::ClassID ) - Sum ( Participants::Inactive ) 

I am also basing my post on Inactive being a boolean number containing 1.  If it is text then Comment's response is best.  :-)

Edited by LaRetta

Share this post


Link to post
Share on other sites
comment    1,381
Posted (edited)

I assumed the Inactive field is a Number field which can either be empty or contain 1. Otherwise OP's summary field would not work as described.

BTW, it could also be a Date field - and IMHO that would be better practice.

Edited by comment

Share this post


Link to post
Share on other sites
DreadDamsel    0

Still can't get it to work.

Number_of_children - number, Lookup (source is a calculation which counts the number of entries in a list field)

Inactive_Activity - a number, set as Boolean configured so that non-zeros=1 and zeros=0 (source a value list with 1 as the only entry)

I need a calculation to go on a field named    total_children_inactive_in_class such as (but I can't get to work):

    if(Inactive_Activity=1 ; sum(Number_of_children) )

Once I have this number, subtracting it from the total of children (which I have) is a simple matter. I need for the admin staff to be able to see both a number of active and inactive children, as if they all turn up at once we exceed our legal limit.

Share this post


Link to post
Share on other sites
comment    1,381

Does the attached work for you?

Note that it's not necessary to sum the inactive children and subtract them from the total. Just sum the active children.

--
P.S. Please update your profile to reflect your version and OS.

 

 

ActiveChildren.fmp12

Share this post


Link to post
Share on other sites
DreadDamsel    0

Comment - Brilliant - was the solution completely. Works like a charm.

This has been the last component to getting the project complete and our Community Centre now has a fully-functioning Contacts and Booking Management system.

Everyone's help is absolutely fantastic on this forum and has gone a long way to helping me complete this project which I undertook on a voluntary basis for the organisation.

Thank you

Lesley Wheeler
Chair
Karingal Neighbourhood House In. 
Frankston, Vic, Australia

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


  • Similar Content

    • By 123
      Hey,
      I have the following problem. I'm working on a solution for the owner of several restaurants. Each restaurant has meetings once or twice a month. How would I structure the tables so that I could have a layout on which I select a restaurant and then inside a portal I get a list of all employees. On the portal for each row I want to have a button or checkbox to mark if an employee is present or not.
      I already have the following tables:
      Restaurants, Employees and Meetings
      So far I have a relationship between the Restaurants and Employees table which I use to assign employees to a restaurant, and a relationship between the Meeting and Restaurant table, which allows me to show all employees that work at a selected restaurant using a portal on the meetings layout. How do I proceed to solve my problem? Do I create another table MeetingAttendees or something similar that I use to keep track of people attending a meeting? How would I create a relationship then to allow me to mark certain employees as present and absent?
      Thanks in advance,
      Mike
    • By docasar
      Hi,
      I have just downloaded the 360works Email plugin Sample and I am doing some tests to check whether this would solve my problem. I would like to be able to download all emails from my email account into "messages" table and then on each customer record show those emails which are related to that customer via a portal based on a relationship between, but what I can not figure out how to do it is that the portal would show but types of emails those marked as "from" emails and those marketd as "to" emails. So far I have created a relationship between Customers::email  ------- Messages::From and the portal would show all "from" messages. If I would set the relationship as Customers:email ------ Messages:To, the portal shows all "to" messages, but what I am trying to make is a join portal that would show by date both "from" and "to" messages, then I plan to make a Conditional Formating saying that if Customers:email = Messages::From then all text in blue, and all text in green when it is TO.
      Is this something that can be achieved?
      Thanks a lot for any tip,
      Luis
       
    • By TJ53
      I guess people are using a similar technique for creating and editing portal records after the new FM16 card window feature, any posts or links about this?
      In any case, this is how I'm doing it (sample attached) ... any feedback appreciated!
      Create or edit portal records.fmp12
    • By wedgeman
      So i'm dealing with two tables.
       
      1.  VisitTable.
      2. GraphicsTable
       
      in a VisitTable layout, i have one or two portals related to the GraphicsTable, each related by a different factor.  One portal would be related by a Date field, the other would be a ClientName field..
      My desire is to click on a thumbnail in either one of these portals, and have that selected image be displayed in a separate/larger image window (which could be a container field in the current table, Or another portal?  I don't care how - i just need to have it show up) 
      I don't know the best method for doing this (ie., click thumbnail to have image fillin a separate location).  In this particular instance, I don't want this to be a separate window, but to display in a container field view on the current window/layout. 
       
      how do I accomplish this?
      thx
       
       

    • By wedgeman
      Table 1 (Visits) is a clinic visit table, with numerous fields of data.
      Table 2 (Prescriptions) is a related table for prescriptions given on a particular visit. Table2 is related to Table1 by fkey  - - -    T1ID.
      Table 3 (Patient) is a patient table (contacts).  PatientID is the identifier for Patient and is related in Visits,Prescriptions, and Patient
      I need to have (in a Visits layout) a portal (self-related by PatientID), which shows a list of previous visits to the clinic by this patient.
      On this portal I need to show any (1 or many) Prescriptions (from Prescriptions table) which would've been given during that particular Visit
      So it's in essence a need to show 2 foreign tables (1 being a self-view by PatientID and the other being the Prescriptions but filtered by PatientID
      I have been unable to figure out a good method for this.
      My only thought has been to give up on working *IN* Visits, but creating a separate table ("Combiner"), and in Combiner, hold all Visits and Prescription data.
      Could I also have a Combiner table that's simply an active "calculation" table, where these two tables are 'looked up' on an as-needed basis, combined, and fed into Visits via a portal? (so it's not actually held data, but is looked up as needed)
      what's the preferred method?
×

Important Information

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