attaching second image
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)]
Set FIeld [HairColor::CustomerID ; $CustomerID ]
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!
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 )
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.
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
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?
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"
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
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)
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.
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.