3 Replies Latest reply on Jun 20, 2015 11:58 PM by GuyStevens

    Count Phone Number Occurrences For Loyalty Program



      Count Phone Number Occurrences For Loyalty Program




      Im trying to make a loyalty program in file maker and was in the process if trying to use phone numbers as a method of figuring out number of visits. I was wondering if there is a way that I can could the amount of times a specific phone number appears in the phone number field?

      Say A person has the phone number 555-555-5555 and each time this person comes a new record is created with this phone number. The counter for times this person has been at the store should reflect the amount of records with this specific phone number inputed. 

      Is this possible?

        • 1. Re: Count Phone Number Occurrences For Loyalty Program

          Sure. This can be done with something called a Self Join. 

          Atleast if the phone number is in the main table and thats also the table that contains the other instances of the phone number.

          First create a Summary field in your table. In the settings for the summary field select "count" and then a field that is never empt. Like the ID field.

          Then create another table occurrence of your table by going to "File" - "Manage" - "Database" - and then the relationships tab.

          Select your table and click the green plus signs. You will now have a second box of your table.

          Now create a relationship (just drag the one field from the one table to the other table and a line will appear) between the phone number field in the original table and the phone number field in the new box ( Table Occurrence ) 

          Done, go back to your layout and add a new field to your layout. The field you need to add is the summary count field that you created. Only make sure it comes from the second box you created. Not from your original table.

          This one probably has a "2" in the name. 

          This will give you the count of all the people with the same phone number.

          I hope this helps. 

          Greetings Guy

          • 2. Re: Count Phone Number Occurrences For Loyalty Program

            Hey Guy, 

            That worked perfectly! Another question, how can i make it so a persons phone number can only be entered once per day. This is to ensure that duplicate punches are not made in the database. I guess the best way to describe this would be to compare date and phone number entered and not allow the record to be created.

            • 3. Re: Count Phone Number Occurrences For Loyalty Program

              You are correct. Two things to compare means a relationship based on two fields.

              First you need a field where the date is entered. This could be a field where the date is entered automatically. 

              Do this by creating a date field and going to the field settings. Under "Auto Enter" you choose "Creation Date".

              Then in the relationship dialogue you create another box for this table by using the two green plus signs.

              Maybe give this box (Table Occurrance ) a logical name so you know which one is which. I suggest you add _DateCheck to the name.

              Then you make two relationships in stead of one. You connect the date fields and the phone number fields.

              Now you have to decide at what moment the check needs to happen. I assume we will be entering the date automatically. So then it needs to happen right after you entered the phone number.

              So you need to make a script that is performed as a script trigger on the Phone number field. And this needs to be after you have entered the phone number and are exiting the field.

              Like OnObjectSave or OnObjectExit.

              The script needs to check if a related record exists in this new Table Occurrence we just created.

              So the script will be something like:

              If ( not IsEmpty ( Table_DateCheck::ID ) )
                  Show Custom Dialogue ( This phone number has already been entered today )
                  * Other actions
                  Go To Field ( Next field after Phone Number )
              End If

              The "* Other actions" part can maybe clear the phone number field or delete thee record or whatever you need it to do.

              Greetings Guy