11 Replies Latest reply on May 17, 2015 9:32 AM by philmodjunk

    Having Issues Adding Records

    GilbertSolorio

      Title

      Having Issues Adding Records & Carry Forward Customer ID when adding new record

      Post

       

      I created a filemaker database containing 4 layouts:

             
      1. Bio Demo
      2.      
      3. Hair Color
      4.      
      5. Perm Wave
      6.      
      7. Skin

      Each layout is a record and within the record I have several fields.

      The Bio Demo is where the user will mostly be.  In this layout I have a Tab Control consisting of 3 tabs (Hair Color, Perm Wave, and Skin).  For each tab, I inserted the portal control so it displays related records associated to the Customer ID and to its respective record.   On the first tab "Hair Color" I added a button at the bottom "Add Hair Color".  When clicked, it takes me to the Hair Color layout and I'm able to enter the information I need.  (1) First Issue is that it doesn't carry over the Customer ID, I have to enter it (which is a problem).  When I go back to the Bio Demo layout, I can see that record in the tab.  

      When I click "Add hair Color" a second time, it takes me back to the Hair Color layout.  I have to enter the Customer ID because again, it doesn't carry over the ID.  But when I create the record, and I go back to the Bio Demo layout, the second record is not shown on the Hair Color tab.  But if I click on this second record, it takes me directly to the second record.    

      Of course, after I get this tab working the way it should, I will need to duplicate this on the other tabs.  

      I hope someone can help or point me in the right direction.  I will attach images

       

       

      image1.jpg

        • 2. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
          philmodjunk

          In your portal, do the following test:

          Enter layout mode and drag the portal a few pixels. Do the portal row fields move with it? IF they don't, the portal does not "own" the fields and the fields will only display data from the first related record. To fix this, drag the field out of the portal with the mouse. Release the mouse button and then drag them back without releasing the mouse button until the field is fully inside the boundaries of the top portal row. Drag the portal again to make sure that the fields move when you move the portal.

          For carrying forward the customer ID, Here's one simple approach:

          Enable "allow creation of records via this relationship" for the "hair color" table in the relationship between Your bio table and your Hair color table. (double click the relationship line). Now you can add new hair color records directly in the portal by entering information into the empty fields of the "add row" that appears just below the last portal record. No scripts or layout changes needed.

          Or if you must change layouts to get everything the way that you want, set up your button to perform a script when clicked:

          Set Variable [$CustomerID ; Bio::CustomerID ]
          Go to Layout ["Hair Color" (HairColor)]
          New Record/Request
          Set FIeld [HairColor::CustomerID ; $CustomerID ]

          • 3. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
            GilbertSolorio

            Hi Phil, when I put the script you provided, I get the following message (attaching image).  For some reason the message didn't come out on the image.  The message is saying "The specified table cannot be found"

            Your suggestion for the portal issue I was having is now working after following your suggestion. Thank you!

             

             

            • 4. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
              philmodjunk

              You have entered a script where you should enter a calculation. That won't work. Scripts are entered via Manage | Scripts (FileMaker 13) or "Script Workspace" ( FileMaker 14 )

              • 5. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                GilbertSolorio

                Thank you once again, your suggestions worked!  Still trying to figure out scripting as I am new and you can tell I don’t know much.

                I was told yesterday they wanted something else as part of creating this new record and not sure if this can be done. 

                For example, when I hit “Add Hair Color” button it takes me to the HAIR_COLOR layout and automatically inserts the CUSTOMER_ID for me (you helped me with this one).  What they also want is to pre-populate certain fields from the last time they had a hair service done.

                Here is a better description.  Customer ID 2000 has been at our school twice, on 4/10/2015 and 4/30/2015.  This is what the records look like:

                CUSTOMER_ID = 2000
                HC_SERVICE_DATE = 4/10/2015
                HC_COLOR_LINE = Redken
                HC_PROC_TIME = 30 min
                HC_STUDENT_STYLIST = Sarah
                HC_INSTRUCTOR = Samantha
                 
                CUSTOMER_ID = 2000
                HC_SERVICE_DATE = 4/30/2015
                HC_COLOR_LINE = Product Line 2
                HC_PROC_TIME = 20 min
                HC_STUDENT_STYLIST = Sarah
                HC_INSTRUCTOR = Samantha

                 

                The client came in today to get something done with her hair.  I hit “Add Hair Color” button to create the following record:

                CUSTOMER_ID = 2000   <--automatically carries forward the ID from BIO_DEMO layout
                HC_SERVICE_DATE = 5/15/2015   <--automatically is created from script
                HC_COLOR_LINE = Product Line 2  <--this needs to come from the last time they were here
                HC_PROC_TIME = 20 min    <--this needs to come from the last time they were here
                HC_STUDENT_STYLIST =          <--leave blank
                HC_INSTRUCTOR =                    <--leave blank

                 

                When creating the new record, notice how it brought the Customer ID.  (1) I would like the Service Date to add today’s date automatically.  Also (2) I want the HC_COLOR_LINE and HC_PROC_TIME to populate from the last time they were here.  The reason is that majority of the time, the customer will get the same service from the last time they were here.  So I would look at all their HAIR_COLOR records and select the highest date.  If any changes are made, the staff member can change the Color Line or Proc Time to something else.  But for the most part, these stay the same.  This creates less data entry.  The student Stylist and Instructor can vary from customer visits so we leave those fields blank and request the staff member to fill these in.

                I was able to figure out how to do #1.  Added the following line at the end:  “Set Field [ HAIR_COLOR::HC_SERVICE_DATE ; Get ( CurrentDate ) ]

                Just need help with #2.

                 

                Thank you!!!

                Gilbert

                • 6. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                  philmodjunk

                  I can describe what to do to get that result, but I can also suggest something that might be even better. When you set up the new HairColor record, it would be possible to list all past visits by that customer in a scrolling portal with the most recent visit listed first. Clicking a button in the row of this portal could then run a script that populates the color line and proc line fields from the selected record which would allow you to quickly accommodate a customer that says, "I didn't like the color I got last time, I'd rather get the color from X visit or y date."

                  Both methods require adding a new Table Occurrence of your HairColor table so that you can then add a relationship between your current table occurrence for HairColor and the new one. A Table Occurrence is what we call the "Box" with the table's name found in Manage | Database | Relationships. It's possible to have multiple boxes on that tab which all refer to the same table. Open Manage | Database | Relationships, click the HairColor table occurrence box and click the duplicate button (Two green plus signs) to make a copy of this table occurrence. You have not duplicated your table, just created a new way to refer to the same table.

                  a) What you have been asked to set up:

                  Link the original HairColor table occurrence to the new copy by CustomerID. Double click the relationship line thus created and click the sort button on the New HairColor TO's side of the dialog box that opens. Specify a sort order that sorts the related records by HC_Service_Date in descending order. Also select the HC_Service_Date fields on both sides, select the ≠ operator and then click Add to add this pair of fields to the relationship. Now click over to Fields, find and double click the field definition for HC_COLOR_LINE to open field options, select the auto-enter tab and click the "Looked up Value" check box. In the dialog that appears select the New Table Occurrence of HairColor as the "look up from related table" and click the HC_Color_Line field. The sorted relationship setting ensures that the first related record will be the most recent previous visit. The looked up value setting ensures that the HC_Color_Line from that visit is copied over when your script enters a customer ID. Repeat these settings for the HC_PROC_TIME field.

                  b) My suggested alternative:

                  Link the two table occurrences by customerID as in the previous option. This time, there is no need to add another field or a sort order to the relationship. Instead, Put a portal to the new table occurrence on your HairColor Layout. In Portal Setup, specify that the records by sorted by service date in descending order. After adding the fields you want to this portal, put a button in the portal row next to them. Set it to run this script:

                  Set Field [HairColor::HC_PROC_TIME ; HairColor 2::HC_PROC_TIME ]
                  Set FIeld [HairColor::HC_COLOR_LINE ; HairCOlor 2::HC_COLOR_LINE ]

                  Your field and table names may be different from mine so adjust your actual script to use the actual names in your database.

                  Caulkins Consulting, Home of Adventures In FileMaking
                  • 7. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                    GilbertSolorio

                    I created the HairColor table occurrence and did "a".  But when I hit the "Add Hair Color" how does the data get populated to the new record?

                    • 8. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                      GilbertSolorio

                      I actually decided to do option 2 after thinking about this a little more.  I already had a portal so do you want me to change the related record from HAIR_COLOR to HAIR_COLOR 2?  Also under the Relationship tab for HAIR_COLOR and HAIR_COLOR 2, do I have to set each field equal to each other?  Currently I have Customer ID equal to each other?  Perhaps this is why when I click the "new" button, it doesn't carry forward the data to the new record.

                      This is the script I have for the new button you had me create in "b"

                       

                       

                       

                      • 9. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                        GilbertSolorio

                        Here is an update - still trying to get option "b" to work with no luck:

                        For Customer ID 2000, they have two visits (first one is the most recent visit and the second is the oldest record):

                        Date of Color Formulation:  5/15/2015
                        Color Formulation:  Test
                        Color Line:  PRAVANA
                        Processing Time:  30 min
                        Special Instructions:  Same as last time
                        Student Stylist:  Juana
                        Type of Color Service:   2-Color Weave
                        Instructors Name:  Kristy Stewart

                         

                        Date of Color Formulation:  5/13/2015
                        Color Formulation:  None
                        Color Line:  Redkin Shades
                        Processing Time:  5 min
                        Special Instructions:  None Provided
                        Student Stylist:  Julie
                        Type of Color Service:   All Over Color + Full Weave
                        Instructors Name:  Dawn Martin Hall

                        When I click the “+” button, on the most recent record, it creates a record however the information being populated is from the first record:

                        Date of Color Formulation:  5/16/2015
                        Color Formulation:  None
                        Color Line:  Redkin Shades
                        Processing Time:  5 min
                        Special Instructions:  None Provided
                        Student Stylist: 
                        Type of Color Service:   All Over Color + Full Weave
                        Instructors Name: 

                        Note:  Student Stylist and Instructor’s Name is blank (this is expected)

                         

                        • 10. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                          GilbertSolorio

                          I think I figured out the problem but want to make sure if this is okay or if I should worry about issues in the future.  Attaching image.

                          The script does work but I'm not using or referencing table #2.  And not sure if this is a problem.

                           

                           

                           

                          • 11. Re: Having Issues Adding Records & Carry Forward Customer ID when adding new record
                            philmodjunk

                            That's a perfectly acceptable and simpler application of Option A and what you were originally asked to set up.

                            Your script that attempted "B" looks correct. Maybe needed a commit records after setting the customer ID and service date.

                            Your relationship needs to be as specified in my original description: Match by ID's but then include a "not equals" operator and service date match fields to keep the record from matching to itself. You would not match by any of the other fields. This relationship also needs a specified sort order, but from what you report, it seems that you have that detail correctly specified.