Show Last Member ID Number in Approval Form

[LeeInKC]

In our club CB application, when a new member submits the registration form, an e-mail notification goes to our club secretary. In that e-mail is a link to an "approval" datapage where the secretary manually enters a new member ID number and checks an "active" checkbox after checking to see that the new member has paid the required dues. Is there a way to display the last member ID previously assigned so the secretary can assign the next sequential ID? Our IDs are somewhat unique, in that they contain a letter followed by two digits for the year joined, a hyphen, then the sequential number of the member starting from the first member. We currently are in the 3100s for that number, i.e. we have had 3100+ members join the club since the beginning.

[ruthallen]

There are several ways to do this. May I suggest you add a drop-down field to your approval form with an auto-complete field and your secretary can type in the membership number she intends to use to make sure it's available. Also, make sure your member ids are unique on your table by sorting them and reviewing for matches and make sure the member id field is unique also (warning - always copy your objects before editing design)./RA

[CrestMineralsLtd]

I have a problem also a problem in my ID.

[LeeInKC]

Thanks for the response Ruth. I tried making the member ID the unique value for the record, however "blank" is a value, and if we get more than one new registration hitting the database before the secretary has assigned a new ID, any after the first one are rejected because of duplicate values... two or more records with "blank" member IDs.

One idea I had was to have the registration go into a queue to await approval, but I don't know how to do that.

Another idea was to add an autonumber field to assign a unique record ID number. The problem with that is I don't know how to go back and assign such numbers to the existing 3100+ records.

Any thought about those possible solutions?

Probably some important information... this database has existed for 11 years and we are just now mounting it in Caspio. It was previously administered on an MS SQL server with custom-written admin apps by someone other than me. Other than a few small glitches such as the one above, it has gone pretty smoothly. I really like Caspio!

[ruthallen]

You're welcome. That is why I suggested a manual type of solution...because you have been using the system for a while and your secretary is comfortable with the method of assigning member IDs. So, don't make the member id field unique. Keep it like you had it. However, add a field to the members table to assign an 'autonumber' in addition to the member id your secretary assigns. This way, when a record is created on the members table, it will automatically assign a unique autonumber to that record and the secretary can add the member id to the record at a later time because, as you have discovered -- unique fields do not allow for 'blank' values. Let me know if you need any more help. I can be contacted directly at - setupyoursite@yahoo.com for emergency help or assistance with your project/s. I'm trying to check this forum a few times a day and, if it will help other Caspio users, let's try to keep our posts here (unless you need emergency help :-).

[ruthallen]

Oh, about assigning autonumbers to existing 3000+ records. Simple. First, if you haven't already done so, add a field to your table for the autonumber (but don't call it autonumber yet, call it something else). Export your members table to Excel and perform an 'autofill' to each row (record). Excel assign a sequential number (1, 2, 3, --) to each record in a matter of seconds and then just re-import the table. Change the table design for your autonumber field to autonumber and make it unique. Remember to copy your table first as a back-up before you do anything with it. If you are unsure how to do this I can do it for you just contact me at my email address.

[LeeInKC]

Thanks again, Ruth! I think the autonumber field to assign a unique record ID is the solution. However, do you know if there is a way to go back and auto number the original 3100+ records? No way am I doing that manually!

[LeeInKC]

Ooops, cross-posted! I am fairly comfortable with Excel, so your solution should be pretty simple for me to do.

Getting back to the original question, rather than having the secretary have to look up the last member ID assigned, I really would like that number to appear on the approval form (which is actually a report). Surely there's a way to fetch it into the form. BTW, I don't do Javascript!

[ruthallen]

Don't worry. I don't recommend Javascript when there is a Caspio solution :-). Yes, there is a way to 'fetch' the data. When you export the table into Excel, sort the records before you have it auto-fill the autonumber field. Sort your table by member ID field, then do the auto-fill. Export it back into CB. Edit the datapage for the form - having the drop-down or list sort in descending order and it will present the record with the highest autonumber. And there are formulas I can give you that will fetch that member ID but if you are comfortable with seeing your data and editing records manually it's just more efficient to see that your data is being retrieved correctly (reduces errors). Have the drop-down or list give the secretary valuable information like the member id, member name, date entered, etc. You could do a drop-down or list that displays the last 5 or so entries on the form always collapsed with no need for auto-complete because there are no entries to make - it will display the last 5 records all the time. However, I understand if you still want a formula to fetch the last member ID used so let me know and I will give you the command syntax.

[LeeInKC]

I will play around with your idea, Ruth. I think I understand the concept. I'll report back how it turns out. I hate when people ask for guidance, get some, then never come back and tell folks how it turned out!! :roll:

[ruthallen]

What is your problem Crest?

[LeeInKC]

Watch out Ruth, I think he's a spammer!

[ruthallen]

Thanks Lee!...I was wondering why I couldn't reply easily???

[LeeInKC]

Well, I am reporting back as promised. Successfully exported the database to Excel and added the autonumber field and populated it with autofill. However, I noticed that Caspio has added another field at the end of the existing fields and each of the 3100+ records now has a unique alpha-numeric entry that looks like this...

0xA78690C92023D576BBD32373B579CE1D6430F9E05F8041315710AA08C9B911D4FFC94AFDA64E4F08B922692A3D7A4F6922AEEBA34AC8D747F2AA0310FB50AD00ECB4BDC0D5BF

Does anyone know what that is for? Should I leave it when I import the file back to Caspio?

Lee