1 2 Previous Next 16 Replies Latest reply on Jan 7, 2010 10:22 AM by philmodjunk

    How to let one relate field equal to another in the script

    Dracozhu

      Title

      How to let one relate field equal to another in the script

      Post

      I am using FileMaker Pro advanced 10.

       

      I am writing a script, that allow me to auto creat a new record in Table 2 when click a button on Table 1.

      However , I try to auto input relate number from Table 1 into Table 2. I tried the commands, setfield, inserttext, all not working.

      Here is what I tired.

       

        • 1. Re: How to let one relate field equal to another in the script
          philmodjunk
            

          You are putting your expressions such as "Orders:: order code" into the repetition box and this shouldn't go there.

           

          Use set field correctly and it should work for you.

           

          Many new users get confused the first time they try to add Set Field to a script. See the following link for step by step instructions:

          Selecting an "All" option with checkboxes

           

          • 2. Re: How to let one relate field equal to another in the script
            Dracozhu
              

            Sorry, I dont think that the right link

             

            I think I explian it in a wrong way.

             

            So Here.

             

            I had Table 1.

            Inside it, I has any array field which called ProductCode.

            Which mean, it can add up lot's different Code.

             If I add a new entry for Product code, and click the butten run the scipt. 

            It will define whether it aleardy in the Table 2. 

            If not, it will auto creat new recode for me, if yes just go to that recode

            However, I have no idea, How array works in Filemaker, That make my scrigled.

            I think the repitition is just array?

            • 3. Re: How to let one relate field equal to another in the script
              philmodjunk
                

              No, that's the link I intended. Scroll down towards the bottom and you'll find the step by step post I am recommending you take a look at.

               

              In your script...

               

              Lines like:

               

              Insert Text [Kilts Order:: Order Code[Orders:: Order Code]]

               

              Tell me that Orders:: Order Code was entered in the repetition box in the specify field dialog.

               

              Correct syntax for this script step is:

               

              Insert Text [Kilts Order:: Order Code ; "literal text"]

               

              Note the absence of the extra pair of []. Since Insert Text won't work for you any way, you should use Set Field like this:

               

              Set Field [Kilts Order:: Order Code ; Orders:: Order Code]

              • 4. Re: How to let one relate field equal to another in the script
                Dracozhu
                  

                Ya, Thanks a lot.

                I understand in [] is the repetition number

                 

                But I am confused about the relation if I click.

                I had a portal table, I assign the scrpit we mention about.

                I wantto get the number for the repetition number that I click on

                I used Get ( ActiveRepetitionNumber ), But it alwasys showed 0.

                 

                (the portal table is relate to another table call Table 3)

                And I tried to auto record for table 2

                But I dont think the problem is here.

                 

                I just wonder, the Get ( ActiveRepetitionNumber ), with click, is this will not create a relation?

                Kind Confused with that

                 

                here is the new scripit, I set $X is the repetitionNumber, But always showed 0

                so it always jumped out from the second if statment, never create the new record

                • 5. Re: How to let one relate field equal to another in the script
                  philmodjunk
                    

                  So you have a repeating field located in a portal?

                   

                  I missed your earlier reference to a repeating field, my mistake there.

                   

                  Get (ActiveRepetitionNumber) returns the number of the repetion where your cursor is located. If it isn't in any field, it returns 0.

                   

                  Where is your cursor when you click the button?

                   

                  If you have Filemaker Pro advanced, I'd enable the script debugger and run this script watching each step execute and using the data viewer to track the value of your variable and your fields.

                   

                  If you don't have advanced, you can insert Show custom Dialog steps and use it to display the value of various fields and your variable at different points in your program.

                   

                  Just before the second If statement, I'd put in

                   

                  Show custom dialog [$X & ", " & Orders::Kilt Code[$X]]

                   

                  To see what values are actually stored in the varible and the Kilt Code field.

                  • 6. Re: How to let one relate field equal to another in the script
                    Dracozhu
                      

                    my portal table is related to repetition

                     

                    Table 1 has a repeted number of Table 3

                     

                    Table 2 only one relate repetition data from Table 1.

                     

                    Table 1 is the Order

                    Table 2 is the Kilts Order

                    Table 3 is the Product

                     

                    I had use the debug system, the problem there is $X is 0 all the time

                    I tried different posibility that should happen, It always jumped out from the second if

                     

                    Also, I put my cursor on the one of the field of the portal table.

                    It is not working if I put on a repetition field box, because It will make the entile box as on butten, Only portal table can be seperate.

                     

                    But I think I know the problem here now, It went to Table 2 to get the number of repetition, But it is in Table 1.

                    Ummmm, Dont know now.

                     

                    • 7. Re: How to let one relate field equal to another in the script
                      philmodjunk
                        

                      I think you're confusing a table of related records viewed in a portal with a repeating field.

                       

                      If I were to set up a repeating field with 5 repetitions, I would define it in Manage | Database | fields and then enter a 5 into the maximum number of repetitions box on the storage tab for the field's options. If you haven't done this for a field, then you don't have a repeating field and get ( activeRepetition ) is not going to help.

                       

                      With that information, do you have a repeating field or simply a portal showing multiple rows on your layout?

                      • 8. Re: How to let one relate field equal to another in the script
                        Dracozhu
                          

                        I Did that already

                         

                        Here, more detailed

                         

                        Product Code in defined in Product Table as single field

                        also defined in Order Table as repetition field

                        I relate, these two

                         

                        so if a click the portal table, it refer to the sipicific one of the product

                        • 9. Re: How to let one relate field equal to another in the script
                          philmodjunk
                            

                          Why is it defined in the Order table as a repeating field?

                           

                          A simple portal of standard fields should suffice.

                           

                          In layout mode, do you see this in your portal row?

                           

                          [Product Code[1]][2            ][3             ][4             ]

                           

                          That's what you should see if you place a repeating field with 4 repetitions on your layout. To see the additional repetitions, you have to specify this option in Field/Control | setup...

                           

                           

                          • 10. Re: How to let one relate field equal to another in the script
                            Dracozhu
                              

                            Becuase, in order table, it can include lots product, no one only order one product

                            they can order some

                             

                            in layout, i can see them

                            • 11. Re: How to let one relate field equal to another in the script
                              philmodjunk
                                

                              "Becuase, in order table, it can include lots product, no one only order one product"

                               

                              Of course that's the case and the invoicing system I set up here where I work enables lots of individual items to be listed on the invoice also. But there are no repeating fields being used. Just a portal listing multiple rows of normal, single repetition fields. 

                               

                              In browse mode, my portal looks like this: 

                              [Item Name][Quantity][Unit Price] [Line total]

                              [Item Name][Quantity][Unit Price] [Line total]

                              [Item Name][Quantity][Unit Price] [Line total]

                               

                                                               Invoice Total:[InvoiceTotal] 

                               

                              Each of the fields, Item name, Quantity, unit price and line total are single repetition fields that occupy one row in the portal. Since the portal can list multiple rows, the invoice can record multiple items purchased on a single invoice. How does that compare to your layout?

                              • 13. Re: How to let one relate field equal to another in the script
                                Dracozhu
                                  

                                Ya,

                                I think I did another method without using repeating fields.

                                Could you give an example about that?

                                • 14. Re: How to let one relate field equal to another in the script
                                  philmodjunk
                                    

                                  I just did. :smileywink:

                                   

                                  Step by step:

                                   

                                  You would create two tables, to start: Invoices, LineItems.

                                   

                                  Define fields

                                  in Invoices:

                                  InvoiceNumber (set this up as an auto-entered serial number on the auto-enter tab in field options)

                                   

                                  In LineItems:

                                  InvoiceNumber (number field)

                                  ItemName (text)

                                  ItemQty (number)

                                  UnitPrice(number)

                                  LineTotal (set up as a calculation field ItemQty * UnitPrice )

                                   

                                  Click the relationships tab and drag from InvoiceNumber in one table to InvoiceNumber in the other.

                                  Double click the relationship line and select "Allow creation of records via this relationship" for the LineItems table. (choose the delete option for LineItems also).

                                   

                                  Go back to Field definitions for the Invoices table and add a calculation field, InvoiceTotal (set it up as Sum(LineItems::LineTotal) )

                                   

                                  Now click OK to exit Manage | Database and find your Invoices layout.

                                  Use the portal tool to draw a rectangle on your layout where you want your list of items purchased.

                                  Select LineItems as the table for this portal.

                                  Choose any other options you want here (you may want to experiment to see which ones work best for what you want here.)

                                  Select the Itemname, ItemQty, UnitPrice and LineTotal fields for inclusion in this portal. (Don't select InvoiceNumber).

                                   

                                  Place the InvoiceTotal field on your layout beneath the bottom of the portal.

                                   

                                  Save your changes and try filling out your invoice. You should be able to enter Item quantities and unit prices and see the totals calculated for each row with an invoice total shown at the bottom.

                                   

                                  Can you get that to work for you?

                                  1 2 Previous Next