8 Replies Latest reply on Oct 4, 2013 10:47 AM by philmodjunk

    Calculation of records based on contents of a field

    schmity

      Title

      Calculation of records based on contents of a field

      Post

           I'm trying to add a box(field) in the footer of a layout that will continuously show the number of records that exist with the status "New" in the Status Field.

            

           I'm using this database in a parts dept to tracks the status of ordered parts.  Were trying to eliminate paper parts requests. 

           The mechanic would enter the part in a New Part Layout via IWP, which would create the record with a status of New.  My problem is how do the clerks in the parts dept know there are new parts requests they need to action, without constantly having to do a search for "New" or clicking a button that would run the search for them.

           I thought the simplest solution would just be a box in the footer of the layout that would refresh automatically with the number of records that had a status of "new"  I'm open to other suggestions though.

           thanks!

            

            

        • 1. Re: Calculation of records based on contents of a field
          SteveMartino

               Why couldn't you have a script that runs (triggers) automatically every time the mechanic commits a new record of parts entered?

          • 2. Re: Calculation of records based on contents of a field
            schmity

                 That was my first thought, but I couldn't figure out what the script would do.....i.e. how would it notify someone else that there was a new request.  Wasn't sure what my options were (I'm fairly new to file maker & programming).  My first idea was a popup window that would display to everyone logged into the database with a certain privilege set saying "New Part Request" that they could just see and then closeout of.  I tinkered with the new window script, but it didn't seem to do what I wanted to.

            • 3. Re: Calculation of records based on contents of a field
              schmity

                   I'd like to do this without creating another field, as this is a 1 table database.  Tinkering a little more, I found the Insert symbols option, that seems to be close to what I want to do, I would just like it to show the total of records based on the criteria of the status field containing "New". 

              • 4. Re: Calculation of records based on contents of a field
                philmodjunk
                     

                          I'd like to do this without creating another field, as this is a 1 table database

                     That may not be a realistic expectation. FileMaker is a Relational database. It's true power cannot be tapped without linking tables in relationships and that almost always requires more than one table.

                     What we have here is a list of "ready for cashier" records that continually updates on computers operated by our cashiers. Other employees create the records and click a button to change the status to "ready". Those records automatically appear in the list and then disappear off the list when a cashier selects the tag, completes the transaction and prints a receipt for the customer.

                     That sound like something you could use in your system? (just think "new" instead of "Ready for cashier".)

                • 5. Re: Calculation of records based on contents of a field
                  schmity

                       I'm not opposed to creating another table, I just meant I only had 1 at the moment.

                       What you said about the ready for cashier is exactly what I am looking for. 

                       I found a few articles after refining my search a little bit (one pertaining to the cash register example, one about push notifications and another using the ontimer script to open a custom window.  I'm going to tinker around with some of those for a while....maybe I can make something work.

                        

                  • 6. Re: Calculation of records based on contents of a field
                    philmodjunk

                         Here's what we use, though I'd probably have used Install OnTimer Script if it had been available in the version that we were using at the time I set it up:

                         Set Error Capture [on]
                         Loop
                            Enter Find Mode [] ---> clear the pause check box
                            Set Field [YourTable::Status ; "New" ]
                            Perform Find []
                            Sort Records [Restore ; no dialog ]---> you don't need this step if you want to list the records in the order that they were created.
                            Pause/Resume Script [1 second]
                         End Loop

                         This script starts up automatically when your users open the layout used to display this list. This creates an infinite loop so all buttons on this layout are set to either specify the "halt" option in button setup or perform a script that ends with the Halt Script script step to stop the looping.
                           

                    • 7. Re: Calculation of records based on contents of a field
                      schmity

                           I think I figured out something that will work

                           I created a new layout and when users of a certain privilege level open the database, it automatically 2 windows, 1 with the normal layout where they can edit records and another that looks similar but all the fields are locked from editing. Within this layout only an ontimer script runs to perform a find on everything with a status of new every 30 seconds.    As new records are created, it automatically lists them out and as the records are updated to a status other than new, they drop off of the second screen.

                           Can you see any flaws in this that I may not realize?  Im testing it right now and it seems to be working.  I just wasn't sure if the constant perform find would cause any issues.

                      • 8. Re: Calculation of records based on contents of a field
                        philmodjunk

                             Under certain circumstances, (users are accessing via WAN instead of LAN, iOS clients, extremely large numbers of records in the table, summary fields, aggregate function calculations, conditional format expressions...) it might.

                             But as long as you keep your layout simple, host over a Local Area Network and not a Wide Area Network, I would expect it to work OK.