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.
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.
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]
Set Variable [$AdjusterID ; value: Adjusters::AdjusterID]
Go To Layout [JoinTable]
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.
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
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.
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.
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?
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.
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.
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.