1 2 Previous Next 18 Replies Latest reply on Jul 25, 2011 10:24 AM by margotjacqz

    Create new record in table B from table A

    MIM

      Title

      Create new record in table B from table A

      Post

      From table A, when I enter a unique identifier into a field,

      1. I want to make a button that runs a script allowing me to create a new record in table B that copies that related identifier

      2. I want that identifier association to pull over portal/related field information

      I have written a script that will give me my custom dialog box, go to the correct layout in table B, and create a new record, but will not copy the identifier or upload the information it should link to.

      Also, I should note the portal/related fields (I have tried it both ways) view correctly in existing data records, but don't automatically appear in new records regarless of whether they are created by the script (that isn't working anyway) or if the unique identifier is manually entered.

      Please help me figure out how to get the portal/related record information to display in the new records (regardless of how they are created) and please help me to write script that can hopefully create a new record in different table and at least insert the unique identifier.

      Thanks!

        • 1. Re: Create new record in table B from table A
          LaRetta_1

          From parent table, button which says "New child record" would look like this:

          Set Variable [ $parentID ; ParentID ] ... this ParentId is what you refer to as 'unique identifier' that binds the relationship
          Go To Layout [ layout based upon child table ]
          New Record/Request
          Set Field [ ParentID ; $parentID ]

          ... what you do from here will be determined by what you want to achieve.  You can stay here and fill in the remaining child data or you can return to the parent and fill in the child data in the portal.

          • 2. Re: Create new record in table B from table A
            margotjacqz

            Hi, I am having a similar problem and have created a script like the one above - I thought. And it is not working.

            Specifically my script, Run from a button on a record in People (TO PeopleContactAll) :

            Set Variable [ $PID; Value:PeopleContactAll::PID_ui ]
            New Window [ Name: "New Action" ]
            Go to Layout [ “Action New Entry x people” (ActionJoin_people) ]
            New Record/Request
            Set Next Serial Value [ ActionJoin_people::ActionID ]
            Set Field By Name [ ActionJoin_people::PID; $PID ]
            Commit Records/Requests

            The new window layout is based on a join  table between TOs for Action and People, linked thorugh ActionID and  PID. I'd expect the new window to have the PID and ActionID fields filled in. Not happening.

            • 3. Re: Create new record in table B from table A
              philmodjunk

              This isn't the correct script step here:

              Set Next Serial Value [ ActionJoin_people::ActionID ]

              But this step:

              Set Field By Name [ ActionJoin_people::PID; $PID ]

              Looks correct, so I don't see why you aren't getting the PID field filled in the the PID field from PeopleContactAll. You may need to either use FileMaker advanced's data viewer and script debugger to confirm that $PID is getting the value you expect. If you don't have Advanced, you can temporarily add a show custom dialog step just after the Set Variable step and use it to display the value of $PID.

              • 4. Re: Create new record in table B from table A
                margotjacqz

                Umm. The dialog shows that $PID is picking up the correct PID value from the PeopleContactAll record. But it is not setting the field in the join_table layout. I can type it in and the link is made showing the name from PeopleAction. ?

                About the set next seriel value idea ... The layout in question has a field for ActionID (indexed) and related fields from the ActionTable (link by ActID - unique), the intent to create a new Action record joined to the oringinal person record. Fields include <ActionID>, and <::ActionSubject>. The allow create new records are checked both directions. If I type into ::ActionSubejct I get an error message "cannot be modified until ActionID is given a valid value."

                There is clearly something I am missing/ not understanding in the layout. The join table fields are all replaceable, the layouts fields are enterable,


                Possible ignorant mistake - the join table records do not have their own unique key. problem? they have been working fine elsewhere, but this is the first time used as a basis for a layout.

                • 5. Re: Create new record in table B from table A
                  philmodjunk

                  Set next serial value is used to reset the serial value to be assigned to the next new record.

                  The error message is telling you that ActionID must receive a value before you can enter data into ::ActionSubject as this is a related record and FileMaker can't determine which record in that related table until ActionID receives a value.

                  Just spotted the other problem.

                  Change: Set Field By Name [ ActionJoin_people::PID; $PID ]

                  To: Set Field[ ActionJoin_people::PID; $PID ]

                  • 6. Re: Create new record in table B from table A
                    margotjacqz

                    1. Tried SetField [] also not setting.

                    2. I get that. So why doesn't the portal work to create a new related record?

                    • 7. Re: Create new record in table B from table A
                      philmodjunk

                      1. Please post your script so that I can see it.

                      To post a script to the forum:

                      1. You can upload a screen shot of your script by clicking the edit link to your original message and uploading it there
                      2. You can upload a screen shot to a file share site and post the download link to a new response you post here.
                      3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
                      4. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                      5. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional look to your script.

                      .

                      2. The portal, with "allow creation of..." enabled for the portal's table, works to create a new record in the portal's table--not a different related table--when you enter data into a blank row of the portal.

                      Your portal is to the join table, not the Action table. Until there's a valid number in ActionID, there's no way to link to or create a new record in the action table.

                      • 8. Re: Create new record in table B from table A
                        margotjacqz

                        1. Script with new variations:

                        Set Variable [ $PID; Value:PeopleContactAll::PID_ui ]
                        New Window [ Name: "New Action" ]
                        Go to Layout [ “Action New Entry" (Action) ]
                        New Record/Request
                        Set Field [ ActionJoin_people::PID; $PID ]
                        Commit Records/Requests

                        Revised with new layout based on Action rather than join table. ::PID is in portal.  Still no joy - $PID not getting entered.

                        2. not clear. Allow me to summarize. I have:

                        Action Table with <ActID> (unique) and <ActionSubject>

                        People Table with <PID_ui> (unique) and <PeopleName>

                        and ActionJoin_People with <ActionID> and <PID> foreign keys [yes I realize now naming could have been clearer <s>]

                        Want to create an Action Record already linked to the People record I start from.

                        - I have a successful layout based on Action with link to People <ActionJoin_People::PID> that can be made manually using drop down list.  SetField  didn't seem to work in portal, so that's why I tried using the join table.

                        • 9. Re: Create new record in table B from table A
                          philmodjunk

                          Apologies for the confusion

                          I see now that you need to add a new action record and then link it to the current People record...

                          Your script needs to create new records in both the join and the action table.

                          Set Variable [ $PID; Value:PeopleContactAll::PID_ui ]
                          New Window [ Name: "New Action" ]
                          Freeze window
                          Go to Layout [ “Action New Entry" (Action) ]
                          New Record/Request
                          Set Variable [$ActionID ; value: Action::ActID]
                          Go To Layout [ActionJoin]
                          New Record/Request
                          Set Field [ ActionJoin_people::PID; $PID ]
                          Set Field [ActionJoin::ActionID ; $ActionID]
                          Go to Layout [ “Action New Entry" (Action) ]

                          Note: I'm assuming that ActID is an auto-entered serial number so that creating a new record in this table generates a new ID automatically.

                          This script leaves you with a new window open to the new action record so that you can enter the data needed for this new record. Closing the window will return you to your People Layout where the portal to the join table will use the added join record to link to and display data from the newly added action record.

                          • 10. Re: Create new record in table B from table A
                            margotjacqz

                            Thanks Phil. I was beginning to suspect a second record/window was needed but hadn't faced up to how to do that. Great to have a route mapped. (yes you are correct on assumption ActID)  Still not clear on why $PID not working previously, but that may be a moot point.

                            Will be able to get ot this in full later and report back.....

                            • 11. Re: Create new record in table B from table A
                              philmodjunk

                              At first, it was not working because you used Set Field by Name. Then it wasn't working because you were on the wrong layout--both mistakes the above script will avoid. (Also, make sure that your set field steps do not show two sets of []. If you do, you haven't set up that script step correctly.)

                              • 12. Re: Create new record in table B from table A
                                MIM

                                This seems impossible but I don't have a "Set Variable" command in my scripts list (on the left when you are editing a script). Is there something really basic I am missing? My script reads very similar to the first one posted by LaRetta (but with a custom dialog box):

                                Show Custom Dialog Box []
                                If[Get(LastMessageChoice)=1]
                                Go to Layout [(Child)]
                                New Record/Request
                                Set Filed [Child::ID; Parent::ID]
                                End If

                                This displays my custom dialog box, creates a new record in the child table from the button on the parent table, but doesn't bring over the info. And I can't insert a "set variable" command as I mentioned above.... Any ideas?

                                • 13. Re: Create new record in table B from table A
                                  philmodjunk

                                  What version of FileMaker Pro are you using? 

                                  In recent versions, it should be found near the top of the list of steps in the Control group of script steps.

                                  • 14. Re: Create new record in table B from table A
                                    MIM

                                    Ooooh, 7. I just saw they are offering 11, so old. But I don't have the authority to update. Do you know if there is a comprable action in 7?

                                    1 2 Previous Next