1 2 Previous Next 21 Replies Latest reply on Apr 12, 2012 9:16 AM by philmodjunk

    Record count function

    JacquesL

      Title

      Record count function

      Post

      I have a table in which I need a field to contain the number of records found in another table (after a query has been executed). Can this work? If yes, do I need to link the tables and what function does the required count?

      Thx.

        • 1. Re: Record count function
          philmodjunk

          Yes it can work and maybe you will want to link them in a relationship. It depends on how you intend to "find" the records in this other table. Perform a find? Use a portal filter? Use a filtered Relationship?

          If you use a script to perform a find on the other table, the script can use get ( foundCount ) to count the records found and can then load a global variable or field with that count in order for it to display on the original layout. (finds should be performed on a layout that refers to the table being queried.)

          • 2. Re: Record count function
            JacquesL

            i'm just not sure WHERE the script should be placed... and how to load the global variable...

            yes, just beginning with FMP...

            • 3. Re: Record count function
              philmodjunk

              All scripts are created in the script editor so there really isn't any other place to put them. You'll need to learn how to create a script.

              Can you describe in more detail what you are trying to do?

              Not only will that help me to put up an example script, it may reveal other options for what you are wanting to do that do not need a script at all as there are calcualtions, relationships, etc that can be used to count records in another table.

              • 4. Re: Record count function
                JacquesL

                I know how and where to write a script... :-)

                I guees I should have said from where the script should be "called" for execution...

                Ok, I have customers to whom I deliver a basket of vegetables every week, and I have multiple locations where these goods are delivered. These customers have individual records and locations are recorded in a field called "locations" (it is a text field - check box format). I need to generate a weekly list of the number of these baskets to be delivered by location - it is important to note that this number has to be calculated every week because cutomers might join or leave at any time, and there are some weeks when customers are away (this info is stored in another table) so we have to exclude them form the count.

                Now I want to keep the weekly record of number of baskets delivered, and I need to keep this info in a seperate table. This table has to include "date", "number of baskets by location", which takes into account how many customers are away...

                thx

                • 5. Re: Record count function
                  philmodjunk

                  Glad I asked for more detail. That's a very different scenario than what I had imagined. Wink

                  First, how have you structured your database?

                  Most databases for sales and delivery are built around these core tables/relationships:

                  Customers----<Invoices---<LineItems>----Products

                  This would assume that you have more than one type of vegetable basket and it's possible for a customer to order more than one type in a single delivery. You create one record for each delivery to a customer and LineItems lists the type and quantity of items purchases and is usually updated from a portal located on the invoices layout. Products serves as a "catalog" of items offered for delivery/sale to customers.

                  Does your database have such a structure?

                  • 6. Re: Record count function
                    JacquesL

                    No no. Items in the baskets are not part of the database. Nor are invoices. Not for now anyways.

                    For now, I only have a cutomer table which has over 400 entries, one for each customer. The table has a field "basket format" (small and large) and a delivery location (A, B, C, D, E...). Locations and formats are what I need to sum every week, taking into account weeks when the customers are away (e.g. baskets are not delivered). I have a fixed season for deliveries (17 weeks).

                    So:

                    Table: customer

                    Fields: name, phone, email, etc., delivery location, format

                    Table that I want:

                    Location A - small format: number of baskets

                    Location A - large format: number of bsakets

                    Location B - small format: number of baskets

                    and so on...

                    Locatiion C - large format: number of baskets

                     

                    is this more clear?

                    • 7. Re: Record count function
                      philmodjunk

                      Does this mean a customer can receive only one type of basket?

                      You've indicated that you need to record which baskets were actuall dilivered. That will require an additional table. What I see here allows you to document what you expect to deliver but you have no means to record what as actually delivered.

                      I undestand that this is not what you have, but you should consider that type of format as a possible future design. Each table servers a specific function common to most businesses that sell products and/or services to customers.

                      • 8. Re: Record count function
                        JacquesL

                        Yes, only one type (size) of basket.

                        What I expect (or need) to deliver is computed every week and is no different than what IS delivered. So I only need one table to compute what I need to deliver.

                        • 9. Re: Record count function
                          philmodjunk

                          I find that hard to believe. You never, ever get a request for more than one basket? You never, ever fail to deliver? (They moved, house burnt down, can't locate address due to error in address given...)

                          Nevertheless, you can easily produce a summary report that lists each basket type and the number of records with that basket type.

                          Create a new layout based on your table. Define a summary field as the "count of" your format field.

                          While In layout mode, double click the body label to open part set up and change this layout part to be a sub summary part "when sorted by" your format field. Put the summary field and your format field in this sub summary part of your layout. You can drage the bottom part boundary up to remove unused space.

                          Now show all records and sort by the format field and you should get a report that lists one row for each value in format and the number of records that store that value.

                          • 10. Re: Record count function
                            philmodjunk

                            Oh yes, be sure to use list view, not form view to view this layout.

                            • 11. Re: Record count function
                              JacquesL

                              this works well,  I suppose that the results in the summary fields can be pushed in another table.

                              but I don't see how it can work for all combinations Location-Format (that is 7 locations and 2 formats), unless I use a script to exectute a search with a double condition.

                              Please note that locations are not the customers houses... but rather fixed pick-up locations...I should of mentioned that at the start.

                              • 12. Re: Record count function
                                philmodjunk

                                Try it and see.

                                The sorting can group by format, and by location as well. It just requires two sub summary parts--one that specifies when sorted by location and one that specifies when sorted by format. You can sort by location, then by format to see a sub heading and sub total for each location and a break down listing totals by format. (You can also reverse the sort order and sub summary part order on the layout to group by format with a break down by location if that is more useful to you.)

                                • 13. Re: Record count function
                                  JacquesL

                                  matter settled.

                                  more headaches (and fun) on the way on other tasks...

                                  thanks

                                  • 14. Re: Record count function
                                    JacquesL

                                    this works just fine.

                                    but is there a way to store the value of these summary fields in a separate table (for further calculations or reference)?

                                    1 2 Previous Next