3 Replies Latest reply on Jan 5, 2009 1:39 PM by TSGal

    Calculating a unique incident number for customers incidents

    tictoc

      Title

      Calculating a unique incident number for customers incidents

      Post

      Greetings all.  I'm a brand new user with a few hours of training time so I'm looking for some help from more experienced folks.  So here goes...

       

      I am in charge of a project that needs to log customers incidents for reporting purposes.  I have x number of customers who each need their own incrementing serial to identify their own incidents.  I assume I'll need a customer table, but really not sure where to start.

       

      The incident number would be:  incident date + customer ID + incrementing serial

       

      Formatted:  2008.12.16-AZL-001

       

      The incident date will be on the main incident entry screen so that's where that should be pulled from, as will a customer field (drop down list?) and the incident number itself.  The customer ID (which is not the same as customer name, but an abbreviation referenced in the customer table) will be in the customer table as will the serial I assume.

       

      Bonus!  I would like to be able to reuse a customers incident serial number when an incident gets cancelled for what ever reason.  It should be flagged as "CAN" and at the same time that number gets put back into the pot to get reused.  I suppose I'd build a "CANCEL" button for this.  This way I have an actual count of incidents that does not include cancellations.

       

      If I'm not clear, by all means, let me know.  If I need to throw in a screen shot of my layout, I can do that too.

       

      Thank you in advance for any help.

        • 1. Re: Calculating a unique incident number for customers incidents
          TSGal

          tictoc:

           

          Thank you for your post.

           

          There are a number of ways to do this, and others may also chime in with a different and more efficient way of doing this.

           

          Yes, you will need a customer table to hold customer information.  However, each customer may have a different number of incidents, so having all that information in one file with your customer information would be cumbersome.  Instead, put your incident information in another table and link the customer table to your incident table through a key field.  In this case, we'll use your "Customer ID" field.

           

          To create a new INCIDENT table, pull down the File menu and select "Manage -> Database".  Click on the Tables tab and create a new table "INCIDENT".  Then, click on the Fields tab and start creating fields.  Be sure to include the following: 

           

          Customer ID

          Date (Date) 

          Incident ID (Text)

           

          You will probably have a description of the incident, resolution, etc., so create those fields as well.

           

          Once the fields are created, click on the Relationships tab, and you will see a graphical representation of your tables.  Click the "Customer ID" field in the CUSTOMER table and drag it over to the "Customer ID" field in the INCIDENT table, and let go.  A line now connects the two tables together, and the "Customer ID" field now appears at the top of each table.

           

          Double-click the icon on the line that lies midway between the two tables, and an "Edit Relationship" dialog box is displayed.  You will notice on one side has the CUSTOMER table and on the other side has the INCIDENT table.  On the side of the INCIDENT table, near the bottom, put a check mark in the option to "Allow creation of records in this table via this relationship".  That way, you can be looking at a Customer record and add a new incident directly.

           

          Also, at the bottom, but a check mark in the option "Sort records", and another dialog box appears for sorting your records.  Click once on the "Incident ID" field, then below that, select Descending order, and then click "Move".  That is, we want the related records to be viewed in a descending list.  That is, the most recent incidents listed first.

           

          Click "OK" to close the Sort Records dialog box.  Click "OK" to close the Edit Relationship dialog box.  Click "OK" one more time to return to your database file.

           

          If you look at the Layouts (top left pop-up of your screen), you will see a new layout added for INCIDENTS.

           

          While in your CUSTOMER layout, pull down the View menu and select "Layout Mode".  This allows you to modify the layout/form.  On the left side of the screen, you will see a number of tools.  Click on the tool just below the oval tool.  This is the portal tool, and it allows you to draw a box and see information from a related table.  Find an empty space on your layout and click on one end and drag downwards diagonally, and you will see a box being drawn.  Let go when you have a size that works for you.  Next, a dialog box appears asking you "Show related records from:" with a pop-up.  Click the pop-up and select the INCIDENT table.  Click OK, and then you will be prompted for fields.  Select the Date and Incident ID as a minimum.  You can always bring in more fields later.  When finished, click OK.

           

          Now, you can see your portal with those two fields (if not more).  Pull down the View menu and select "Browse Mode".  Now, for any record, click on the portal, and you can add information that will be automatically linked to the current Customer record.  You may or may not want this, but I thought I'd include it to demonstrate how this works.

           

          Creating an Incident ID (Number) is a bit different, because we have to look at a number of factors/variables.  That is, "Is this the first incident for this date?"  "If not, what is the last incident number?"  "How do we increment the serial number?"

           

          One way to accomplish this is via a script, and then we can attach this script to a button to automate this process.

           

          Pull down the Scripts menu and select "ScriptMaker".

           

          Press the bottom left icon to create a new script.  Name this script "Add Incident".

           

          On the left side of this dialog box are the functions/steps that can be used.  Double-clicking on these functions/steps move them over to the right side for your script execution.  Your script may look like the following:

           

          Set Variable [$counter; 1 ] 

          Set Variable [$incident; Get (CurrentDate) & "-" & Customer ID & "-" ] 

          If [Count (INCIDENTS::Incident ID) > 0]

             If [Left (INCIDENTS::Incident ID; 10) = Get (CurrentDate) ]

                Set Variable [$counter; Right (INCIDENTS::Incident ID; 3) + 1] 

             End If

          End If

          Set Variable [$incident; $incident & Right ("00" & $counter; 3) ]

           

          ---------

           

          This part of the script initializes two variables ($counter to 1, and $incident that includes today's date and Customer ID).  We then look to see if there are any previous related records (count is greater than zero), and then check to see if the date of the most recent incident equals today's date.  If so, then we grab the last three characters of the previous Incident ID and add one to it.  Finally, we put this all together into one string with the new Incident ID.

           

           

          The entire script may look like this.

           

           

          Set Variable [$cust; CUSTOMERS::Customer ID]

           

           

          Set Variable [$counter; 1 ] 

          Set Variable [$incident; Get (CurrentDate) & "-" & Customer ID & "-" ] 

          If [Count (INCIDENTS::Incident ID) > 0]

             If [Left (INCIDENTS::Incident ID; 10) = Get (CurrentDate) ]

                Set Variable [$counter; Right (INCIDENTS::Incident ID; 3) + 1] 

             End If

          End If

          Set Variable [$incident; $incident & Right ("00" & $counter; 3) ]

           

          Select Layout ["INCIDENT" ]

          New Record/Request

          Set Field [Customer ID; $cust ]

          Set Field [Incident ID; $incident ]

          Set Field [Date; Get (CurrentDate) ]

           

          Commit Records/Requests [No dialog]

           

          Select Layout ["CUSTOMERS "]

           

          ----------------

           

          When you run this script, you will see a new record in your portal.

           

          I'll stop here for now, as I don't want to overwhelm you.  This should get you pointed in the right direction.

           

          I'm sure there are some steps I overlooked, so if there are any steps that aren't clear, or if you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc. 


           

          • 2. Re: Calculating a unique incident number for customers incidents
            tictoc
              

            Sorry it took me so long to get back to you on this.  It's a little overwhelming, but I think I'm catching on.  I understand most of what you're saying.  :)  A couple of things, though.  First, the Incident date will not always (usually isn't) the same as the current date.  We may not enter the incident report until a day or two after it occurs.  Secondly, the scripts you've written - would those be placed within a button for creating a new incident?

             

            I've been on holidays so just getting back into the project now. 

             

            Thanks again for all the info! 

            • 3. Re: Calculating a unique incident number for customers incidents
              TSGal

              tictoc:

               

              Thanks for the clarification.

               

              If the incident date is not the same as the current date, then that isn't a problem.  Just substitute the Get (CurrentDate) with "Incident Date" field.

               

              The scripts can be put into a button or called manually.  That choice is yours.  If you want to attach it to a button, create a button in Layout Mode (View menu), pull down the Format menu and select "Button Setup" and use "Perform Script".

               

              I know all of this can be overwhelming, but just take it a little at a time so that you understand each step.  If you run into any difficulty or don't fully grasp the concept, let me know!

               

              TSGal

              FileMaker, Inc.