Insert a new portal row in a specific place?

[sbutler]

Greetings, I need the ability to insert a new portal row in between other rows, preferably by using a button on the portal row. I'm sure this is a pretty common need, I just have not been able to find an easy solution. Any advice is greatly appreciated!

[-Queue-]

How is your portal sorted? If the portal is not sorted, how is your relationship sorted? If your relationship is not sorted, you'll have to create your new related record, then duplicate the ones that should follow it and delete their originals, since relationships naturally sort by creation order.

[Chuck]

Here's what I might do. I'm assuming that the portal row won't appear in the proper place once it has been sorted, so a custom sort is needed.

First, each record in the portal's table (Table2 for this solution) will need a field called SortField that will be a number field. The first time a record is added to the portal (i.e., it's the only one in the portal), it will get a value of 1. The initial value doesn't matter too much. You could use 10 or 100 or 3.14159 if you wish, but 1 is easy.

Either the relationship or the portal should be sorted by this SortField.

Assumptions:

- Table1 is the table occurrence that the current layout is linked to

- Table2 is the table occurrence that appears in the portal

- Table1 is related to Table2 based on the field Table1ID (the primary key for Table1)

- A layout called DevTable2 exists linked to the Table2 table occurrence.

- Table1 has three global fields: gTable1ID, gCurrentSortField, gNextSortField

- Table2 has a global field called gTable2ID

- Table2 has a primary key field called Table2ID

- You always want to add a record after an existing record (to add before, you should be able to edit this script to accept a parameter and behave accordingly).

# If there's no portal record, set up our current and next to calculate an average of 1

If [ Count( Table2::Table2ID ) = 0 ]

  Set Field [ Table1::gCurrentSortField, 0 ]

  Set Field [ Table1::gNextSortField, 2 ]

  # If we are inserting after the last row, set up our current and next to calculate .5 greater than the current

Else If [ Count( Table2::Table2ID ) = Get( PortalRowNumber )

  Set Field [ Table1::gCurrentSortField, Table2::SortField ]

  Set Field [ Table1::gNextSortField, Table1::gCurrentSortField + 1 ]

Else

  Set Field [ Table1::gCurrentSortField, Table2::SortField ]

  Go to Portal Row [ Next ]

  Set Field [ Table1::gNextSortField, Table2::SortField ]

End If

Set Field [ Table1::gTable1ID, Table1::Table1ID ]

Go to Layout [ Table2 ]

New Record/Request

Set Field [ Table2::Table1ID, Table1::gTable1ID ]

Set Field [ Table2::SortField, ( Table1::gCurrentSortField + Table1::gNextSortField ) / 2 ]

Set Field [ Table2::gTable2ID, Table2::Table2ID ]

Go to Layout [ original layout ]

# Make sure we're targeting the right portal in case there are multiple portals on the layout.

Go to Field [ Table2::FirstFieldtoEdit ]

Go to Portal Row [ First ]

Loop

  Exit Loop If [ Table2::Table2ID = Table2::gTable2ID ]

  Go to Portal Row [ Next, Exit after last ]

End Loop

Go to Field [ Table2::FirstFieldToEdit ]

Keep in mind, I haven't tested this, having just written it for you here, so I may have not thought of something, but this should give you a general idea of what you can do.

[sbutler]

Thanks, Chuck - that worked great!!! This is a quote system. There will be thousands of quotes, each with multiple line items, so the SortField serial# could get very large. I thought it would be a good idea to reset the SortField to 1 when a new quote is created. In this case, if I go back into an old quote and need to add a line item to the bottom of the portal, the SortField is not going to be in sync with the existing SortField values. In other words, if I add line to the bottom, it may (and probably will) sort up to higher row. I wonder if there is a way to have it check the SortField values and set the next serial # accordingly whenever you visit an existing quote record? Or, should I not be concerned if the SortField value just keeps growing and growing?

-Scott

[Chuck]

FileMaker 7 has numerical accuracy to 400 decimal places. I haven't done the math, but theoretically, I think this will allow 2^40-1 items between each whole number, or about 1 trillion.

For instance, assume there were just three decimal places available. If there are no open averages, then between 1 and 2 you would have this:

1

1.125

1.25

1.375

1.5

1.625

1.75

1.875

2

There are seven spots available with three decimal places (2^3-1). Obviously you won't be using all of these, so, with five records you might have something like this:

1

1.5

1.75

1.875

2

Now there's no room between the fourth and fifth record (the average would be 1.9375, which uses four decimal places). But to get to that level of decimals, we had to add the records in just the right order (1, 5, 2, 3, 4). If we had added them in a different order (1, 2, 3, 4, 5) our SortField would look like this:

1

2

3

4

5

So, in the worse case scenerio, you could only add, I think 402 record, but in the best case, practically infinite (since you have 1 trillion possibilities between each whole number).

What it comes down to is, is the worse case likely to happen. Keep in mind, it doesn't matter if some of the portal records have the same SortField value, so long as there's no combination of SortField/Table11ID that is the same. In other words, if Quotes is your parent table, and QuoteLineItems is your portal table, unless QuoteLineItems is often going to have more than 400 line items, there will never bee a problem, and even if it is, the likihood is small.

Chuck