Insert next value from custom value list

[JoanieAKBoat]

This is probably an easy one for you advanced developers:

I've got a database of boats (I work for a boat brokerage in Alaska, remember me?)

Boat ID's are set by auto serial numbers.

Over the course of the last 3 years, many of the records/boats (IDs) have been either deleted or exported to archives.

We want to keep the IDs to 3 digits, and we want the existing boats to keep their IDs. (Sometimes we re-list the same boat and pull it out of archives.)

So, out of 98 active boats and 250 archived, I've got 650 ID numbers to dispense.

The available IDs are specific (i.e. 108, 109, 113, 119....994, 997).

I have entered them into a custom value list and turned off the auto serial # entry, so a broker can choose the next available value manually and enter into the ID field.

How would one go about writing a calculation or script to automate this?

To take the next value in the value list, set it into the ID field, and then delete it from the value list so another broker won't attempt to use it?

I don't see a calculation on how to identify the next value, much less set it and delete it.

Thanks for your help. Joanie

[JoanieAKBoat]

Help! Out of the 25 people who have looked at my post - I have had zero replies.

Perhaps it was difficult to understand. Let me pose it another way:

Originally I had a field that was auto-entered via serial numbers, increments of 1, unique, not empty, strict data type.

Lets say 1-10 have been used.

Out of those ten ID #'s, five of them have been deleted: 3,5,6,7,9

(So I still have 1,2,4,8,10 *Can NOT be reassigned) and I'll call the 3,5,6,7,9 "available IDs".

How would YOU go about writing a script or calculation that would automatically enter the NEXT available ID?

Perhaps its not a value list at all, perhaps its a looped script that compares counters, or a related ID database, or something else?

THANKS for your responses. Joanie

(I'm in the process of learning version7 but have to deal with this in 6 before I can move forward....)

[Ender]

"Joanie from Juneau", I like that!

You usually would not want the ID number to be reused, to keep any orphan related records from trying to claim the new record as it's parent. But perhaps this is behavior that you want for your boat database. If this is not behavior that you want, then you might reconsider this serialization scheme, or use two fields, one as a real serial number for relationships, and then a "Boat Number" field for display.

Anyway, I don't see a way to do this within an auto entered calc in FM5/6. In FM7 (with the Developer version), you could use a custom function to recursively scan a value list of used IDs to get the first unused ID.

I suppose in FM5/6 you could go with a scripted approach. A loop that starts by setting a global to 1 and checks if there is already a related record with that ID (via a self join from the global to the ID field.) When the loop hits a number that has no related record, you would set that to be the ID of the new record.

[JoanieAKBoat]

Thanks for the comeback, Ender.

The thing is I can't script based on IDs used in this databse as many of the IDs have literally been exported to an Archive file. And they might be imported back into the Active file if the boat gets relisted with us. (This happens often as last year's boat buyer wants to upgrade and lists the boat through us. And the ID# stays with that boat. We can always re-relate it to a new seller.)

These are two separate non-related databases. (I will change that in v7.)

Hence the questions remains-

How to write a calc or script that sets the next custom value into the ID field then deletes that value?

It seems as though it should be a no brainer, such as a replace function, or a 'last' function of a repeating field or something (I've always shyed away from repeating fields.) Perhaps I'm stuck on this value list thing when I could be using a global field....

One last thing: Currently I'm using a simple script that:

shows the last ID# used (in "last used" field),

allows the user to choose the next value from a dropdown list ('go to field' "available IDs" field, 'available IDs' custom value list),

which (w/ 5 second pause) sets the manually chosen value into both the "last used" and the "boat ID" fields.

The value list is set to be editable so once in awhile myself or someone else can 'clean up' and manually delete the used IDs from the list.

Is that a clunky script or what?

thanks for any advice you can give me, Joanie from Juneau

[Himitsu]

I will just give an idea... don't know if it would work... but, you could make a table (FMP7 only) with each record containing the serial number from 001 to 999 so you would have 999 records. Then maybe a check box with a value of "1" that would activate it. Then when you make a button for a new record in your boat table (FMP7 only) the script could go into a loop untill it finds a record from the serial table that the check box isn't checked. Then have it getfield contents and set field to the serial for the boat. Then have it check the box that it is used. Seems a little clunky... but just an idea... but of course, if you didn't notice, you have to use FMP7 or you will have to make each script in both files and have the first script start then perform the script in the other file. It might even look something like a tag team script setup... boat file... runs script... passes it to the serial file... runs script... then passes it back to the boat file and runs another script to enter the field right.

Good luck.. if I come up with something I will post it. I just woke up so my head is not fully steamed up yet.

[Ender]

Sorry Joanie, I thought I understood your needs until you started talking about reactivating an old boat. With that as a possibility, this idea of reusing boat IDs seems like a real bad idea. I don't see how any scheme for selecting from those deactivated IDs will work with the requirement that a deactivated boat could be reactivated with its old ID. Do you see the problem??

Maybe I just don't understand the boat business.

[JoanieAKBoat]

Thanks. That might work, since a looping script would work on records in another file. And the check box idea takes care of used/not used numbers.

If I use this solution in fmp6 now, it will take care of my current issue, so I can get back to learning how to migrate to fmp7!!!

It would be nice to find a solution in the same file, that's why I'm so stuck on a way to manipulate the value list items..... I'm sure its just my ignorance on which script functions are available.

Thank you for considering my little problem.

[JoanieAKBoat]

Hi Ender.

I'm sorry for the incomplete information. Sometimes too much info is worse than too little.

In explanation: When I 'export' the boat, most of the fields are first set into look up fields so the information on that boat goes into 'archives', including the ID number. That boat and # is then deleted from the active file, severing its relationship to the owner (in the related "Contacts" file),(and saving the seller's information in a lookup fields called 'former owner' and 'archaddress', 'archcity' etc).

The boat broker decides if the boat is permanently deleted from both the active and the archived databases, or if it is kept 'in storage' in the archive file. (There is alot of info on the boat, ie make, length, fuel type, engine type, sail or power boat, etc., so it is very convenient to save that data that is already entered.) If and when the boat is relisted, usually under a different name and a new seller, it is convenient to simply import all that data back into the active file and then 'reconnect' via the new seller's ContactID.

I must add, too, the main reason we keep the same in-house IDs has to do with the fact that the old boat information, especially photos, is cached on our website - which was built in MS Access prior to creating the house fmp database and is served remotely. We upload current data only via html table, and do the photo upload directly to the website. (I know, we could publish from here, but the owner wants to do it this way.)

Sooo - now you know more than you ever wanted to know... And you might better understand why I have a custom list of available IDs for new boats.

check it out if you want www.alaskabotbrokers.com

Again, thanks for your time and consideration. J

[stanley]

Joanie:

I had to do something like this once & ended up doing essentially what Himitsu suggested. In your case, having 999 records, and then having a status field. In your case it would be "active" or "archived" or "empty". When going to add a new boat, you'd just find for "empty", sort by number, go to first record, and there's your first available number.

However, this is ultimately a bit of square peg, round hole stuff, as I found out when dealing with the solution I built for the customer...

Good luck.

-Stanley

[JoanieAKBoat]

Thanks! Of course!

I appreciate your input.

Can you think of any way to do that with a check box in a value list within the same file?

(I hope I'm not being too dense about this...)

I'd like to correct the typo on the website I entered earlier:

alaskaboatbrokers.com

Joanie

[Himitsu]

Joanie... about doing that in same file... that, I think, wouldn't even work... remember when you layout any kind of DB, it functions around relationships... ie, each table (file) has a parent, a main item. So if you think like that, it would be a terrible mess to store all the info on one file (table) and have something go through it, I like a script... If I remember correctly, up untill FMP7, the relationship area was messy and very hard to keep organized...infact I don't know if you can even create a relationship that a valuelist can only use for a filter.... maybe you can... but I am sure it is a complete head ache... really FMP7 is very very worth the change over... just the having the table and relationship ability that FMP7 offer, is worth it... let alone all the other improvements... including SPEED...

I wish I could be more help... but I think my mind has purposly forgot FMP6 since I have been working on FMP7 all this year... hahahah

Good luck and let us know what you come up with ... posting your details on how you came up with a solution for this, will help others when they search this forum for a solution.

[JoanieAKBoat]

OK, thanks Himitsu. I agree with you on trying to forget fmp6, but unfortunately, I've got to keep the database working until I finish my fmp7 conversion. As I plan the revamp, I will have separate tables for Active and Archived boats and an ID# table. All ID#s (between 100-999) will be assigned an active, archived, or empty status, and new boats will be assigned the next available empty ID. Simple.

I get it that the value list selection won't work.

Meanwhile, what I'm doing (on fmp6) is allowing the user to "see" a value list of available unused IDs, having set the last used ID into a global field, and they get to choose the next manually. This is VERY clunky, but there are only 2 users who input data for new boats and they're willing to put up with this solution until I get the new system up and running!!!

Meanwhile, I'm still open to suggestions!!