Portals and auto-enter from previous record

[Marc G]

Is there a problem with using the "auto-enter from previous record" option on a field in a portal?

I have created a db where this option works fine. As the user creates a new record, the vendor field is automatically populated with the vendor from the previous record.

However, when I use this db as a child table in a portal, the auto-enter behaves unpredictably. As the user creates a new child record, it populates the field with info from an existing record, but not always the immediate previous record. This produces unpredictable and frustrating results.

Is there a sort issue involved (I tried different sort orders as part of defining the relationship between the parent and child db)?

Or is it just not possible to use this field option for a portal row?

[jasonwood]

Just a thought... Is this database used in a multi-user environment. If two people are adding line items at the same time, the "previous" record might not always be the last record you created.

I don't think you want any sorting of the related records.

[BobWeaver]

My experience with auto-enter previous in portals is that it's unpredictable. It doesn't necessarily enter the data of the last record displayed in the portal, but the last record in the related database which may not be in the portal of the current record. I made up a sample database that gets around this problem. I will try to find it and post it here. Warning, it's ugly!

[BobWeaver]

Okay, here is the sample file. Actually, now that I look at it, the method doesn't seem to be as messy as I thought it was. I threw it together pretty quick a long time ago, and didn't document it too well, so here are some notes to go with it.

There are two files, Main and Related. The portal is displayed in main, and the portal data comes from Related. The portal contains a regular field MiscData, and a field "AutoEnterData" that is auto-entered from the previous portal row (if any). Most of the work is done through several self-join type relationships and three utility fields in the related file. These relationships and fields keep track of the last entered data from the related group of records that appear in the portal. Then, the auto-entered field itself is set up as auto-enter calculation. You can look through all the definitions to see how everything works.

AutoEnterPrevious.zip

[Vaughan]

Warning: I haven't taken the time to actually read your posts above. But I'm gonna take a stab at a suggestion anyway...

The Last() function -- though designed for repeating fields -- also has the ability to pull out the values form the last related record. So you could create a script that uses the Last() function to pop the last-entered data into a global field, create a new related record and pop the stored data into the new record.

It might be possible to do without the script and use the Last() function in an auto-entered calculation somehow: this will need experimentation.

[BobWeaver]

My method doesn't use a script, just some calculated fields and autoenters. But, I wasn't aware that the Last() function would work this way. So, it may very well be possible to simplify my solution.

[djgogi]

The only thing that could screw up everything is the sort order when defining relationship, cause in that case at the instance the new record is created in child file, the funcion Last(relName::FieldName) will look at the last record satisfaying the sort order and not the last created record.

Dj

[jasonwood]

This topic just gave me a great idea!

If you number your line items in this way, and sort by that number, you would then be able, through scripts, to insert line items in the middle of the list (by assigning a number like 2.1 to go after the second line item for example).

This will be helpful in one of my future databases because I want to be able to enter a "bundle sku" on an invoice. A script would then add all of its components to the invoice immediately after the bundle sku.

Yippee!

[Vaughan]

"The only thing that could screw up everything is the sort order when defining relationship, cause in that case at the instance the new record is created in child file, the funcion Last(relName::FieldName) will look at the last record satisfaying the sort order and not the last created record."

That's easy: create a relationship based on the same keys, but don't specify a sort order. The last record will be the newest. Use this relationship for the auto-enter.

[djgogi]

That's easy: create a relationship based on the same keys, but don't specify a sort order. The last record will be the newest. Use this relationship for the auto-enter.

That's what I'm usually doing.

My standard set up for relating two files:

ConstRel const1-->const1 for passing globals ect.

CurrentItemRel gfkID-->pkID one to one relationship

MainRel pkID-->fkID one to many non sorted with allow creation of related records and cascade delete enabled

MainRelationSorted same as above but sorted and with other options disabled

MultiKeyRelations (filtering portals, finds ect)

Dj

Dj

[BobWeaver]

Well, this Last() function is going to greatly simplify another little thing that I have in a database that I'm working on. I've never seen anything about using this on a related field before. It's not mentioned in the manual (surprise!). Thanks for pointing it out Vaughan.

[djgogi]

Last function

Overview

[Vaughan]

Dj -- thanks for taking the time to post the definitive information on the Last function!

Bob -- what goes around comes around. Earlier today I was able to remove an ugly Case() function that had (English) month names hard-coded into it and replace it with a function whereby the last day of the month is calculated as the day before the first of the next month. Yeah! Bloody clever that, a real head-slapper. Thanks LaRetta for mentioning it in your post!

[djgogi]

I like these functions:

PrevMonth=MonthName(Date(Month(Status(CurrentDate)),0,Year(Status(CurrentDate))))

LastDatePrevMonth=

Date(Month(Status(CurrentDate)),0,Year(Status(CurrentDate)))

CurrentMonth=

MonthName(Date(Month(Status(CurrentDate))+1,0,Year(Status(CurrentDate))))

LastDateCurrentMonth=

Date(Month(Status(CurrentDate))+1,0,Year(Status(CurrentDate)))

NextMonth=

MonthName(Date(Month(Status(CurrentDate))+1,1,Year(Status(CurrentDate))))

LastDateNextMonth=

Date(Month(Status(CurrentDate))+2,0,Year(Status(CurrentDate)))

Dj

[LaRetta]

Thanks so much ...

Well this Forum is truly miraculous!! My studies for tonight were on Date functions ... and I've hit the mother lode today I've now added this tid-bit to my stolen (errr, acquired) knowledge base

LaRetta