7 Replies Latest reply on May 12, 2009 1:13 AM by mayor

    2 tables in 1 layout



      2 tables in 1 layout


      I got 2 tables research & expenses link by account_no


      I design input screen/layout that combine the fields from

      those 2 tables, but data can be key-in in table research only.


      Can we combine  fields from 2 table in one layout?


      If can, how?



        • 1. Re: 2 tables in 1 layout

          Yes, you can add fields from related tables.  To add just one related record, choose Field… from the Insert menu and pick a related table (Expenses) from the pop-up menu. Ensure that you have checked the checkbox "Allow creation of records in this table via this relationship" for the Expenses when you edit the Relationship.


          If you have multiple expenses for each research project, add a 'Portal' of the expenses table occurrence to your research layout.  Portals have multiple columns and rows that can be used to add, display, and delete related records from another table.  This way, you can add any number of expenses against each research project.


          For details on how to create portals, please take a look at the topic: "Creating portals to hold related records" in the FMP Help menu item.

          • 2. Re: 2 tables in 1 layout

            TQ Jade for your helpful explanation, I got it.



            • 3. Re: 2 tables in 1 layout

              Jade or any FMP expert,


              I have one more question. I have multiples expences for each research/project. I already design 1 layout that

              combine 2 tables (research & expenses).


              I want to make my system friendly. Let say in same research, I want to make another expenses. So, user no

              need to key-in the same data for research. What the need to do is only add the new expenses data.


              Could plese help me how to do that.


              FYI, my tables are:


              research (research_account_no, researcher_name etc)

              expenses (po_no, invoice_no etc.)


              * relational field : research_account_no



              • 4. Re: 2 tables in 1 layout

                You can either create a script that creates a record in the Expense table:


                Freeze Window

                Set Variable [ $acctnum; research::research_account_no ]

                Go to Layout [ "Expense" ]

                New Record []

                Set Field [  expense::research_account_no; $acctnum ]

                Go to Layout [ Orig ]



                or if you dont mind creating a new record in the portal itself you can just turn on "Allow Creation of Records" in your relationship on the expense table side.

                • 5. Re: 2 tables in 1 layout

                  I have tried to create script and the result as below.


                  Freeze Window
                  Set Variable [ $acctnum; Value:research::research_account_no ]
                  Go to Layout [ "Input Screen" (research) ]
                  New Record/request []
                  Set Field [  expense::research_account_no ]
                  Go to Layout [ Original layout ]


                  I realized that, the is no $acctnum in the line Set Field (line 5) as your given code,

                  I don't know how to add this. When, i run this script it still add

                  data in table research when I want to add another expenses.


                  Please advice me





                  • 6. Re: 2 tables in 1 layout

                    In the set field step, where the calculated result goes, just manually type: $acctnum


                    Also the reason that you are still creating a new record in accounts is because you are not going to a layout based off EXPENSE. Please take a closer look at the script in my earlier post.

                    • 7. Re: 2 tables in 1 layout

                      TQ so much, I got what i mean.