Jump to content
  • 0

Show Last Member ID Number in Approval Form


Question

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.

Link to post
Share on other sites

13 answers to this question

Recommended Posts

  • 0

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

Link to post
Share on other sites
  • 0

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!

Link to post
Share on other sites
  • 0

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 :-).

Link to post
Share on other sites
  • 0

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.

Link to post
Share on other sites
  • 0

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!

Link to post
Share on other sites
  • 0

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.

Link to post
Share on other sites
  • 0

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...