Indexes and auto-generated IDs

Hey Andy, hope the online development is going well, we'll be needing that! ;)

I see that tables like the customer and notes tables etc don't have auto-incremented primary keys - so are you generating the next ID (eg, when creating a new customer) within the application? Can this value be reset? - at the moment, in my test db it's creating customers starting at something like 1168, whereas I'd like to look at the feasibility of starting at 1 etc.

Even if I nuke the records in the lastid and lastid2 tables, RGP is still generating ID's from a persistent internal variable. Resetting the auto-increments in the various indexes has no effect on the behaviour.

(Yes, I know this is hacking a bit on a test db, I'm just looking into RGP's behaviour and the feasibility of implementing something as RGP doesn't have a concept of a public membership number, which my client currently requires.)

0

Comments

24 comments
  • Official comment

    No, it hasn't - as it just didn't prove to be a popular request.  People are either using barcoded keytags or the mobile app - which can use auto-generated barcodes.

  • RGP does not support external modification of the database in any fashion. Additionally, IDs are generated internally and guaranteed to be unique across all objects which is why it behaves the way you see. We cannot support any databases that are externally modified (for obvious reasons as then we don't know if a bug is ours or created by your modification which is puts the database in a state we didn't predict).

    What *exactly* is your client trying to achieve? Perhaps if you explain it I can suggest a best course of action.

  • Yes I know - don't panic! I'm simply investigating at this stage :)

    Their existing system has membership numbers (which are different to the actual customer record ids) and their entire paper-trail admin system relies on this for various reasons - as I've been convincing them to move to RGP, we are trying to minimise a large amount of back end admin work that may well put them off the move - so I'm looking at ways of tackling the issue.

    While I *could* use one of the custom fields, it's not optimal as it would require manual incrementing of the membership number when creating new records with all the associated manual errors that this would create (or make some other system to do this, which is one of my other options).

    Yes - they most likely need to move away from that system and RGP will undoubtedly help, but we need to migrate in as minimally disruptive a way possible. Requiring a few members of staff for a week or more to reorganise five years of physical and computer files is pretty much a non-starter.

    The bottom line is that a fundamental way of how the client has done things in the past relies on a feature that RGP does not support, and I need to figure out the best solution to suit everybody, without being too messy or error-prone, in order to move forward...

  • Where are they getting #s currently? And is there a reason the customer id couldn't be used (if it was made available)?

  • The system that they already use has the concept of membership numbers which it handles automatically, which is how everything is filed.

    For *new* customers *after* the RGP installation, then yes, we could conceivably use the RGP-generated customer ID *but* there are ten thousand existing customers which would need to have specific ID's that have already been assigned - and I cannot control the RGP ID creation operation to be able to choose which ID's each customer has, or to avoid overlaps with existing customers from the old system.

    Also, as you alluded to - RGP doesn't expose the customer record ID in the gui - there is no display for it, which is less than optimal. It may be possible to do a report or search but I haven't gone done this route yet because it seems I won't be able to use customer ID's as I have no control about how they are assigned anyway... Anything I could do (eg, have a little helper tool to copy the ID into the custom field (which *is* exposed in the gui) requires write-access to the db, which obviously I am trying to find ways around...

  • EXISTING CUSTOMERS -

    Are you planning on importing existing customers into RGP? If you provide the customer id in the import data then I can assign that to either the custom field OR the barcode field (why not use the barcode field for the member id?) as part of import.

    NEW CUSTOMERS AFTER RGP DEPLOYMENT -

    Do you care if the numbers are sequential.. if not..

    Prior to deployment, we can "reset" the next ID generated in RGP to a high number (beyond the range of existing customers). And then...

    Option A - I can provide one line of SQL that you can periodically execute within RGP to copy the IDs to the custom field/barcode field

    Option B - I can add a feature that automatically does Option A :)

  • Or....

    Option C - I can provide a feature that automatically assigns an incrementing number to the barcode field and you can set where that # starts. You aren't the first to have asked for this feature :) It's not a hard feature obviously, more just a question of timing and releases since I'm knee deep in the online scheduling.

  • > Are you planning on importing existing customers into RGP?

    Yes.

    > If you provide the customer id in the import data then I can assign that to either the custom field
    > OR the barcode field (why not use the barcode field for the member id?) as part of import.

    Yes, in my test hacking of running manual imports I have been doing this for backwards compatability in order to see how this would work in practice. Haven't thought of using the barcode field - is this the one used for customer tags you can scan on entry? If so, then I'd probably prefer to leave this open as it's a feature that we may well want to use in the future, unless it doesn't clash with that functionality.

    > NEW CUSTOMERS AFTER RGP DEPLOYMENT -
    > Do you care if the numbers are sequential..

    I'd have to check (need to run the options past the client to spot any outstanding pitfalls) but I doubt it's that important.

    > Prior to deployment, we can "reset" the next ID generated in RGP to a high number (beyond the range of existing customers).

    Ok.

    > Option A - I can provide one line of SQL that you can periodically execute within RGP to copy the
    > IDs to the custom field/barcode field

    Yeah which would be one of the options I was considering.

    > Option B - I can add a feature that automatically does Option A :)

    Which would be preferable of course but I think manual would be acceptable enough. (In time I'll be trying to ween them off doing things this way anyway).

    However, it doesn't handle existing customer records so we'd need to find a solution for that otherwise the above won't matter!
    However, I *might* have a solution for this so the above might be enough.

    > Option C - I can provide a feature that automatically assigns an incrementing number to the
    > barcode field and you can set where that # starts. You aren't the first to have asked for this feature :)

    Yeah, but it's the same old thing of upgrading the settings gui, the load/save settings code, the various DB sql when creating customers, handling errors etc and while it's not hard it's enough of an effort that I wouldn't expect you to do it, for us or imminently. I'd rather you continue your existing development commitments anyway :)

    > It's not a hard feature obviously, more just a question of timing and releases since I'm knee deep in the online scheduling.

    Yeah I understand this, appreciate it.

    Also, just so you understand - this is a price sensitive installation. I'm pretty sure the client will upgrade to the premium version in time however I'd like to get them up and running with the free version if I can so they can realise how awesome it is and what advantages it will give them. So I need in the first instance things that do not require a paid-support contract and fees up front, as this makes the sell to switch harder.

    So I'm limited to things I can do here without pestering you too much...

  • I haven't read your entire response yet... but BE AWARE, we must import your data for you.

    You cannot manually create records in the database for the same reason we can't have you modifying the database.

    See this page: http://www.rockgympro.com/?page_id=333

    More later...

  • Yeah I know. I'm not (I'm only doing this in testing on a copy of the database which is not even being used by my RGP installation, as a way of looking at my current data and what RGP's data is to work out transformations and what can and can't be handled.

    I will not be doing it this way for final or using this database. I'd love to be able to as that would simplify things a lot but I have a different approach which I'm also assessing the feasibility of).

    I have read the docs and warnings and will be heeding them, don't worry! ;)

    For our purposes you can assume we are going to manually re-enter everything via RGP's front end.

  • Ok, thanks for understanding.

    I've lost track of the amount of time I've spent fixing people's databases after they modified/created their own records.

  • Ok, existing records are being handled. We don't need to manually set the next ID's high for new records as the id's will I assume follow on incrementally from adding the existing customers manually.

    All we really need for now is access to the record ID somehow.

    When an RGP record is created from a signed waiver, we need to write that record ID on the form and file it. So if the created ID was displayed somewhere, either in the View/Edit page (even in the title bar or something innocuous) or in the main Customer List in the Date Entry application then that would be great - once the record is created, we can either glance at the list (ideally) or reopen the Edit screen and note down the ID on the form and file it.

    Could we maybe do a report for this? - eg Show All Records added today, and include the ID in the report? This would handle the need when adding records (but not when searching for old manual waivers). (Yes, we would eventually move to the much more friendly RGP-supporting way of storing digital waivers)

    Failing that, some batch SQL to copy the record ID into the Custom1 field (if it's not empty) would be doable, it's just a little more manual to do after every waiver or waiver batch. Front desk would be doing this, so it ideally needs to be non-technical to execute and not easy to mess up!

    What are your thoughts on this, Andy?

  • I'm not too interested in exposing the customer record #, as EVERY other user of RGP will be confused by that number.

    I can very easily supply a custom report that will display the record IDs of new customer records (one time $250 charge for me to produce the SQL).

    Alternately, you can write your own custom report if you know SQL. They are very simple if you know SQL. See the Window in Data Entry -> View -> Reports -> Custom SQL Reports. You have investigated the database schema so you obviously know what you are doing :)

    Create a new report and drop in a SINGLE line of SQL. You can use {STARTDATE} and {ENDDATE} as place holders for date ranges if you want them. Reports are readonly so it is safe for you to play. Or just sort on the first contact date field in reverse order.

    I'd be happy to provide SQL to copy over the record-id into one of the custom fields (there are plenty of them) if desired as well.

  • > I'm not too interested in exposing the customer record #, as EVERY other user of RGP will be confused by that number.

    Yeah I understand that might have been the case.

    > Alternately, you can write your own custom report if you know SQL. They are very simple if you know SQL.
    > See the Window in Data Entry -> View -> Reports -> Custom SQL Reports. You have investigated the
    > database schema so you obviously know what you are doing :)

    Yes I did see the custom SQL report thing and thought it would be viable so I'll start there - thanks!

    > I'd be happy to provide SQL to copy over the record-id into one of the custom fields (there are plenty
    > of them) if desired as well.

    That would be cool if you could, thank you.

    I'm really liking RGP so far, this is going to be *sooooooo* much better than our old system :)

  • Incidentally, if you are interested in my import solution and how to stress test RGP - I'm currently running RGP in VMWare Fusion on a Macbook Pro.

    I actually have the Windows front end connecting to my development mySQL server running in OSX (how's that for an unsupported configuration! :) This is just more convenient for me during development and investigating how to drive RGP, that actual installation will be completely standard from a clean initial database.

    Because of the import issue and the avoidance to write to the DB directly, I developed a system to automate RGP's front end.

    Basically, I have some PHP code that pulls the data out of my old system, does the necessary transformations, and writes out an automation script for each customer record. Then over on windows, another script loops through the directory of these and using a free Windows automation scripting system called AutoIt3) remote controls RGP to add the customer data as if it's being manually inputted (adding users, handling duplicates, changing customer types, entering data, switching tabs, adding notes etc).

    While it's obviously slow, I left it running last night and "manually" entered 2000+ customers, it seems to be pretty solid, and at least gives me control about how I'm transforming the data on the way in, specific to our own data's idiosyncrasies, and gives me a repeatable import system.

    So yes, a bit more of a pain to implement than a simple mySQL import, but then sometimes you gotta do what you gotta do...! :)

  • Good on 'ya for figuring out a way to add customers without writing directly to the database :)

    I've personally seen RGP installations with 100,000+ customer records and RGP behaves just fine....

  • Yeah, I meant more the front end / gui stuff. The back end db should be solid, they are fairly small numbers for this stuff ;)

    Incidentally, the custom SQL reports thing works well, I'm doing something like:

    SELECT CUSTOMER_ID, FIRSTNAME,LASTNAME,ADDRESS1, ZIP, CUSTOM_TEXT1 AS OLD_ID FROM customers
    WHERE LAST_RECORD_EDIT
    BETWEEN DATE( DATE_SUB( NOW() , INTERVAL 1 DAY ) )
    AND DATE ( NOW() ) LIMIT 30

    ...to give me the last bunch of records added today. We'll check out the workflow and see if everyone's happy.

  • exactly!

    I've made some pretty full-on custom reports... it gets tricky trying to keep it as a single line of SQL sometimes, but with the right joins/subselects I haven't found a report yet I couldn't put as a single line of SQL.... yet.

    For "new" customers, you'll want to use firstcontactdate.

  • Great, thanks!

  • Hello Beejay and Andy,

    I am wondering if either of you two can tell me what I would use to use check-in as a query variable? I'd like to run a query where I only show members who have checked in within a date range. Seems like this search would work if I could change last_record_edit to something like "check_in" - I just don't know what piece of SQL to use for that. Any help? Thank you.

    William

  • William -

    If you want to see a list of members who checked in within a certain date I recommend customer queries (View -> Customer Queries). You can select Customer Type = MEMBER, Status = OK, and Visit Count between dates >1

  • Andy - this looks promising - using Customer Queries - but is there any way to set the parameters so that I can see a time range? For instance: visit count for members between 10am and 4pm. That would especially be helpful if I could do a range of dates limited by checkin time: Member checkins in December between 10am and 4pm. Doozey!

  • The data is obviously available, but that would require custom SQL either in Navicat or a custom report.

  • Andy,

    Sorry digging this up from the past a little but you mentioned:

    "Option C - I can provide a feature that automatically assigns an incrementing number to the barcode field and you can set where that # starts. You aren't the first to have asked for this feature :) It's not a hard feature obviously, more just a question of timing and releases since I'm knee deep in the online scheduling."

    Has this been implemented? Looking through the settings I can't spot it so sorry if it exists.

Please sign in to leave a comment.

Didn't find what you were looking for?

New post