1 2 Previous Next 16 Replies Latest reply on Apr 23, 2014 11:36 AM by mdphillips

    Caculation based on a value list or total number of records

    mdphillips

      Title

      Caculation based on a value list or total number of records

      Post

           I have 2 somewhat similar questions. I'm working on a solution to track lodging for our group. I have set up 3 related tables - Lodging, Hotel Rooms and Occupant and using portals to display everything in one layout. In the Lodging table I have fields to track the total rooms, rooms occupied and rooms available. 

           Is it possible to have the total rooms field be auto calculated based on the total number of records from the Hotel Rooms table?

           Some events require us to share rooms so in the Hotel Rooms table I have fields for the # of beds per room and a male/female value list if we are sharing.  On the Lodging table I also have fields for for total number of beds by gender.

           Is it possible to have the total beds fields be auto calculated based on the assigned value of male or female?

            

           Thanks!

            

        • 1. Re: Caculation based on a value list or total number of records
          philmodjunk

               And what relationships have you currently defined for these three tables. By adding additional table occurrences of rooms, you can set up more relationships between Lodging and rooms that match records in the manner needed for your different counts:

               Lodging::anyfield X Rooms|All::anyfield (uses the Cartesian join operator instead of =)

               will Match any record in Lodging to all records in Rooms and thus: Count ( Rooms|All::__pkRoomID ) could be defined in lodging to count all rooms.

               Lodging::cMale = Rooms|Male::Gender

               Where cMale is a calculation field: with "Male" as it's calculation expression, will match any record in Lodging to all Rooms designated for Male occupancy.

               Another option is to use the count function inside some SQL queries used with ExecuteSQL if you are using FileMaker 12 or newer.

          • 2. Re: Caculation based on a value list or total number of records
            mdphillips

                 Here's a screen shot of the relationships as is.  Being a rookie I'm still struggling with some of the nomenclature and syntax.  I've been working on this project in part help a group I'm involved with but also to learn Filemaker 13.  I've been looking at sample solutions as examples and have noticed the use of "pk" in ID names.  Sorry to be this dumb but does the pk have to be used or is it more a standard naming convention?

            • 3. Re: Caculation based on a value list or total number of records
              philmodjunk

                   "pk" is a naming convention often used to identify a field used as a tables "primary key". "fk" is often used to identify a foreign key. In your sameple relationships, Logdging::Hotel ID is a primary key if it auto-enters a serial number or get ( UUID ). Hotel Rooms::Hotel ID would be a foreign key. Primary keys uniquely identify each record in the table foreign keys are not unique but are fields in a related table that match to a primary key.

                   Each "box" shown in your screen shot is called a "table occurrence". In the case shown above, they each show the name of the data source table to which they refer. But if you select Hotel Rooms by clicking it and then click the Duplicate button (two green plus signs), you can make a duplicate table occurrence. This new table occurrence is not a new table, it's a new way to refer to the Hotel Rooms data source table. If you click over to the Tables tab you'll see that you have not added a new table, but now there are two "occurrences" of Hotel Rooms listed.

                   This new table occurrence will first be named Hotel Rooms 2, but you can rename it any way that you want if you double click it open up a dialog box for that purpose. To set up the Lodging::anyfield X Rooms|All::anyfield relationship. Use the above method to duplicate the Hotel Rooms table occurrence and rename it to be Hotel Rooms|All. Then drag from any field in Lodging to any field in Hotel Rooms to select the needed match fields. Then double click the relationship line to open up a dialog box where you can change the = operator to X.

                   But from your relationships shown, are you really sure that you want to count all records in Hotel Rooms? Won't you want to count all Rooms in Hotel Rooms for a single Hotel? That count would not be a count of all records in the Hotel Rooms table.

              • 4. Re: Caculation based on a value list or total number of records
                mdphillips

                     Thanks for all your patience and help!  I will try the new table occurrence you mentioned above and yes, you are correct about the room count.  Ultimately what I want calculated is:

                     Total number of rooms per given hotel

                     Total # of beds per given hotel

                     Total # of rooms by gender per given hotel (if applicable)

                     Total # of beds by gender per given hotel (if applicable)

                      

                     Thanks Again!

                      

                • 5. Re: Caculation based on a value list or total number of records
                  philmodjunk

                       A calculation field defined in Lodging as:

                       count ( Hotel Rooms::Hotel ID )

                       With Number selected as the result type.

                       will return the total number of rooms for a given Hotel.

                       A relationship such as:

                       Lodging::Hotel ID = Hotel Rooms|Male::Hotel ID AND
                       Lodging::cMale = Hotel Rooms|Male::Gender

                       could be used to match to only those records for a given hotel that are designated for Male occupancy. cMale could be defined as "Male" with text selected as the result type if Gender in the Hotel Rooms table stores the word "Male" to identify rooms for that gender.

                  • 6. Re: Caculation based on a value list or total number of records
                    mdphillips

                         Have I mentioned that you're awesome!  Dumb newbie question - what does the"c" stand for in cMale?  I currently have the gender field set up as a check box values.  It would be a problem to change that to straight text if it's easier to calculate. 

                         As I mentioned before, being new I struggle with nomenclature & syntax.  I found a Script Step Reference for FM, which is very helpful. Do you know if such a reference exists for calculations or could you suggest any helpful training guides?  Right now I've kept my head above water by watching the webinars and attempting some reverse engineering of sample solutions. 

                    • 7. Re: Caculation based on a value list or total number of records
                      FentonJones

                            

                           The standard reason for "c" at the start of a field name is to mean: calculation field. 
                            
                           Personally, I also use either "_cName" or "z_cName", to push the field to either the top or bottom when sorted by "field name." I do this to keep (almost all) calculation fields separate from "data entry" fields.
                            
                            Some people do not use "c" (or other); they either do not sort fields at all (or sort by) "creation order", and some others sort fields by "field type", others with their own "custom order" (which means you have to keep it in order manually). "Field name" seems simplest to me. It is easily switched to, by clicking on "Field Name" (top right).
                            
                           Sometimes one will want to switch to another, then back (to what is the "default choice"). For example: I cannot quite remember what exactly I named that new field (darn). I switch to "creation order", and there it is, at the bottom of the list. Then I switch back to Field Name.
                            
                           FileMaker remembers the last one you used (on file when local). It also matters whenever you need to choose a field, like for another calculation field, or during a script when wants to use a field. (You can however switch the sort order even during modifying a scripts.)
                      • 8. Re: Caculation based on a value list or total number of records
                        mdphillips

                             Thanks Fenton!  That clears things up a ton.  FM is a great program - it's reasonably user friendly but there are so many options and ways of accomplishing things that it can be overwhelming.  It's great to hear veteran wisdom so I can develop good design habits.

                        • 9. Re: Caculation based on a value list or total number of records
                          philmodjunk

                               c means what Fenton said.

                               

                                    I currently have the gender field set up as a check box values.  It would be a problem to change that to straight text if it's easier to calculate.

                               Why check box values? Does that mean that you might designate the same room and male and female? Check boxes imply to the user that values may be singly selected or in combination. If all rooms are strictly designated as male or female, then a radio button format would seem the better choice.

                               Note value list formats: check boxes, radio buttons, pop up menus, all store data in much the same manner as it would if you formatted the field as an edit box. The only excepting being that if you select multiple values--such as clicking several checkboxes, the values are stored with a return character separating them. So as long as your Gender field only stores a single value, you don't have to change this just to make the relationship work.

                          • 10. Re: Caculation based on a value list or total number of records
                            mdphillips

                                 Got the room count working (happy dance!)  So in building this out.  I'm guessing that if I want to do similar calculations on room/bed availability as I check people in or out, I'd want to do a duplicate occurrence for Occupants?  Here's my new relationship set up.Am I headed in the right direction or steep cliff?

                            • 11. Re: Caculation based on a value list or total number of records
                              mdphillips

                                   My thought was to make it easier/quicker for the end user to check a box rather than type in a value so it isn't a big deal to change the layout. 

                              • 12. Re: Caculation based on a value list or total number of records
                                philmodjunk

                                     Yes, but you can "check the box" with radio buttons and then there is less confusion over whether the user is permitted to select just one or both values in the field.

                                     I don't see an obvious need for another occurrence of Occupants. Assuming that you use a field or fields in Hotel Rooms to check occupants in or out, you can get a count of occupied or unoccupied rooms for a given hotel several different ways using either count in the Lodging table or a summary field in the Hotel Rooms table.

                                     Example:

                                     If you have a field in Hotel_Rooms that has data in it when the occupant has checked in and has no data in it when the occupant has checked out, then:

                                     Count ( Hotel_Rooms::CheckedIN )

                                     Will return the number of rooms for a given Hotel that have an occupant checked in. The total count of rooms minus this figure will give you the total rooms where an occupant is not checked in.

                                     PS. I'm beginning to think that you may need a different table of Rooms that lists each room once that is separate from the the current Hotel_Rooms table where you have one record for each unique combination of occupant and hotel--in the current table, you can have multiple records from the same room--one for each occupant assigned to it. You may or may not need that table, but it's an option to keep in mind as it could be added and linked into your current Hotel_Rooms table (which you might want to rename), and then you have a layout where it would be easy to see who is assigned to each room and whether you have any rooms that currently do not have any assigned occupants.

                                      

                                • 13. Re: Caculation based on a value list or total number of records
                                  mdphillips

                                       Thanks - radio buttons do make more sense.

                                       As for the means of checking in occupants.  I've been looking at & trying to reverse engineer a solution in FM Starting Point that checks assets in\out.  It seems like it could be a good system for filling out the occupant fields. Not sure if I'm explaining this right but it uses a popup window to fill the asset's user fields which are then viewed in portal.  Does that seem like a reasonable & efficient way to accomplish room assignments or is there a better option than popup windows?

                                       I'm not sure if I mentioned this before but some of our events have an open end date. Occupants may change and the duration of their individual stay may differ.  So in your room count example, if I set up (in my mind thinking radio buttons for checked in/out) I should be able to write a script that would add or subtract from the room or bed count depending on which button is checked -correct?

                                       From a learning standpoint, why do you suggest changing the Hotel_Rooms table name and do you have any suggestions for a good practice to follow?

                                       Can't say thank you enough!

                                  • 14. Re: Caculation based on a value list or total number of records
                                    mdphillips

                                         I did a bit of tweeking so hopefully I'm understanding your suggestions. I added a new table called assignments and linked it to the occupants table.  I created a "bed number" field in the assignments table. Could I have the bed number field repeat itself based on the "total beds" and have the available bed count change as beds are assigned? I was thinking that I could use an "add occupant" type button to assign rooms/beds and could show/hide it if a room or bed was empty and maybe even throw in some conditional formatting of the room number. Does that sound reasonable or am I totally crazy?

                                    1 2 Previous Next