Numbering Addresses by Account

[jasonwood]

I'm trying to create a new contacts database where there would be 1 record per account in the "accounts" table, then additional records in other tables such as Addresses, Phones, and Contacts.

I want to setup an auto-enter calculation that, starting with 001, will number the addresses as they are created from a portal on accounts.

I've tried it several ways but can't seem to find a way where it will get both the first entry AND subsequent entries.

Do I need to script the creation of addresses or should there be a way to do it with the auto-enter calculation?

[Ender]

If you just need to use the portal row number for display purposes, using @@ as text in the portal row will number the rows (no leading 0's though).

If you need an Auto-Entered number in an actual field, you can do something like:

In Accounts, create a field nextAddressID (calculation, text result)= Right("000" & NumToText(Max(Address::rowNumber)+1),3) //Uncheck Do not evaluate if field is empty

In Address, create a field rowNumber (text, lookup: Account::nextAddressID)

Put the rowNumber field in the Address portal.

I think the account record has to be commited before the numbering will work, and for sure, numbering will get screwed up if portal rows are deleted.

[jasonwood]

ya that's a little different than how I was doing it - I think that would work.

I actually ended up giving it some thought and decided that scripting it was probably a much better way to go any way. I'm starting to move away from using "Allow creation of related records" and instead using scripts so I have more control over related record creation. It's working well.

Thanks.