3 Replies Latest reply on Aug 7, 2009 10:06 AM by QueryJoel

    Relationship or Portal?



      Relationship or Portal?


      How do I create a relationship between job database and payment of the following conditions:


      1)  Job database is the main database

      2)  Payment - a subset of Job database;  1 job may have more than 1 payment records.

      3)  Job and Payment are separate layouts. 

      3)  Payment must reflect certain fields that are similar on Job database. (Please see example)


      Should 'Payment' be a separate table from Job database or a portal or is there a better way to create this? 

      What is the best way to create a working relationship between the two such that the user is able to seamlessly navigate between the two?   




      Job - Client; Job Description; Job No.; Estimate No. etc.

      Payment -  Payee; Client; Job Description; Job No.; Estimate No. etc.


      The fields highlighted in orange are common fields between the two. 


      Thank you. 

        • 1. Re: Relationship or Portal?

          You need two tables with a relationship that links JobTable::JobID = PaymentTable::JobID. JobID should be an auto-entered serial number and the relationship should be set up with the option "Allow creation of records via this relationship" selected.


          The fields in orange should only be found in the JobTable. In layouts where you display payment records, you can add these fields from the JobTable and they will correctly show the matching data for the current payment record.


          You'll probably want to use a portal. Make layout based on teh JobTable and create the portal specifying the payment table. Now for a given Job, you'll be able to see/edit all the related payment records.

          • 2. Re: Relationship or Portal?

            Don't duplicate info, that's what a relational database is for.


            Job - Client; Job Description; Job No.; Estimate No. etc.

            Payment -  Payee; Job No. etc.

            • 3. Re: Relationship or Portal?
                 Thanks Phil and Mikey!  Sounds good... should work!