9 Replies Latest reply on Sep 6, 2011 10:22 AM by philmodjunk

    Relationship help for tables

    AnnetteSteinberg

      Title

      Relationship help for tables

      Post

      I have two tables that I need relationship help.

      First Table is called JPMS and is for a new job and all its information.  There is a section on JPMS for Adjusters (and their contact info) which is from a related table.  The related table has the Adjuster contact info plus their type (inside/field/independent).

      Sometimes I need to be able to enter more then one adjuster (and this could be a combination of all three types, or three of the same type) from the related table (and all their contact info), but when I use a JPMS foreign key field to relate the two tables and automcally fill in all the related adjuster information, it puts the adjuster info in all three areas of fields I've created for the different adjusters.

      I made this work previously by having three separate related tables for the adjuster types (inside/field/independent).   But cleaning up the tables is problematic.  So I need to be able to have the adjusters be on one table, and be able to create adjuster records in the JMPS table and the Adjuster table.

      Thanks for your help.

        • 1. Re: Relationship help for tables
          philmodjunk

          Is this your relationship?

          JPMS::JPMSID = Adjusters::JPMSID

          Where JPMSID is an auto-entered serial number in JPMS and a number field in Adjusters?

          Do you need to use this portal to create new records in adjusters or just to connect to existing records?

          Sounds like you have a many to many relationship here since a record in JPMS can link to more than one adjuster and an adjuster can link to many JPMS records. That requires the use of a Join table.

          • 2. Re: Relationship help for tables
            AnnetteSteinberg

             Hi Phil,

            Hi - I tried to produce the join table with unacceptable results.

            Where JPMSID is an auto-entered serial number in JPMS and a number field in Adjusters?  Yes, did this. Made a join table, with two foreign key fields, one for JPMS and one for the Adjusters.  The adjusters table has a key field (serial number) as well.

            Do you need to use this portal to create new records in adjusters or just to connect to existing records?  I need to be able to create new adjusters on the JPMS layout (in the portal) and in the Adjuster table (as I've read in other posts - by using the newly created join table).   When I used the join table as the portal, no records were present, and when I used the Adjusters table as the portal, the adjuster records were shown only sometimes.   Also, I had some problems with deleting/removing adjusters (because during data entry, or during course of job, the adjuster might change).  I need to be able to modify the names without deleting them in the Adjuster Table.

            Also, I can use some help in how the portal behaves.  I'd like the portal to have only 3 rows, and when you enter an adjuster name, the portal row stays at the top, then if another adjuster name is selected is goes in the 2nd portal row, and this row does not move to the top.  Same with row three.

            Your help, as always, is appreciated.

             

            • 3. Re: Relationship help for tables
              philmodjunk

              Here's a many to many demo file you can compare to what you have set up: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

              A join table will not, of course, show any data in a portal to it unless you first create records in that join table to link exisiting records on either side of the many to many relatiionship to each other. (In your case, that links an adjuster record to a jobs record.) Deleting a record from this portal deletes the record from the join table, removing the link, but does not delete a record form the Jobs or adjuster tables unless you have enabled the delete option for that in the relatinship--something you should not do here.

              In a portal all records are listed in the order that they are created unless you specify a sort order for either the portal's relationship or the portal.

              To create new adjuster records, you can put a button on your layout that  performs a script like this:

              Set Variable [$JobID ; value: JPMS::JobID]
              Go To Layout [Adjusters]
              New record/Request
              Set Variable [$AdjusterID ; value: Adjusters::AdjusterID]
              Go To Layout [JoinTable]
              New Record/Request
              Set Field [Join::AdjusterID ; $AdjusterID]
              Set Field [Join::JobID ; $JobID]
              Go To Layout [original layout]

              That creates both a new adjuster record and then links it to the current Job record. If you have the fields you need from adjusters located in this portal, you can then fill in the needed info for the adjuster record by typing data directly into these fields in the portal.

              • 4. Re: Relationship help for tables
                AnnetteSteinberg

                 Thank you for the example.  I am now able to select from the adjusters table and it doesn't put the newest portal row on top.  I haven't tried the new script, but I will.

                However, in the set up dialog box for the portal, there is a box to indicate how many rows to show?  I have +95 records that I need in a drop down for selection, and that would get bigger if we add more adjusters.  Is there a way to show the rest of the related table records without having to the change the number of rows everytime in the portal set up dialog box?

                 

                Thanks so much Phil

                • 5. Re: Relationship help for tables
                  philmodjunk

                  The number of values in your drop down have nothing to do with the number of rows in your portal. Your portal rows should only show the number of adjusters linked to the current record--3 or so by your description. And you can include a scroll bar so that there is no practical limit to the number of adjusters you can select for a given project.

                  There are no settings you can use to change how the drop down list deploys, but you will be able to scroll through the list to select an adjuster. There are also ways to trim down the number of listed adjusters with a conditional value list or via alternative, script supported techniques that make selecting from such a long list easier for the user.

                  • 6. Re: Relationship help for tables
                    AnnetteSteinberg

                     So, I'm missing something, because there are only 3 names showsing in the drop down box out of the 95 adjusters I need to be available to select in the portal.  I only need three rows for any combination of the +95 adjusters.   And only 3 show in the selection from the drop down button.

                    I'll check all my links again too.

                    • 7. Re: Relationship help for tables
                      philmodjunk

                      You'll have to look at your value list and how it is setup. If you want, you can tell me exactly what options you selected and I may be able to spot why you are only seeing three names.

                      By any chance do you have some type of "category" or "type" field selected for field 2 in this value list? And did you specify sorting by this field?

                      • 8. Re: Relationship help for tables
                        AnnetteSteinberg

                         Hi Phil,

                        I got the portal to work.  I have another issue with this same portal.

                        There are potentially three adjuster names that can be added to the form.  Adjuster1 (first portal row)/Adjuster2(second portal row)/Bill To Adjuster(?).  The bill to Adjuster is often the same name as portals rows one or two.  But sometimes there are only two adjusters (Adjuster1/Bill To Adjuster) that need to added, and I can't skip rows in the portal, so how do I create a new portal which relates to the same adjuster table. So far everything I've tired hasn't worked.

                        Thank you, 

                        P.S. I'm still working on the script your provided me to create new adjuster records, so far I'm not getting it to work.  But I need to get the above issue corrected first.

                        • 9. Re: Relationship help for tables
                          philmodjunk

                          Every portal row should represent a record in the join table. Add a status field to the Join table--not adjusters and format it as a check box field with a single value. To desginate an adjuster as the "bill to" adjuster, click the check box.Add this field to your portal.

                          This keeps all your links to adjusters in one portal.