Sanity Check – Making Changes to a Key Field of a Rather Large Parent Table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress

The Request

I’m hoping you all can help me out and make sure I’m not about to do anything that will break my app.  I’m the only admin in my company, and my only real database training was a brief stint at a former job, the initial QuickBase training when we signed up, and the hacking and slashing and brute force learning I’ve done over the past year.  While I’d say that I’m pretty strong given the circumstances, I’m a little unclear on the inner workings on relationships and key fields.  Can you look over the following plan to and let me know of any potential issue?


The Problem

Almost every table in my app has the customer tables as a parent record.  Before importing our customer list last year, I didn’t really think to do much data cleanup (I was a rookie, what can I say?).  Now, we have some customers and leads listed in all caps, which causes various issues, most notably with mobile users (sometimes it can “break” the link).  What’s even more fun is that the customer name is the key field.  Since I'm going in to make updates anyway, there are some other minor consistency issues I'd like to fix along the way.

Previously, I learned the hard way that if you edit a key field of a record, you need to manually link the record to its children records.  And since we’re talking about over 6000 customers, that seems like a really bad plan to me.

Except for one, all of the children tables are in the same app, and the other app in question is so rarely used that I could manually fix the records, if needed.  We’re only talking about ten records, there.

Since we have such a large number of customers, we use the “the values in this field depend on a selection in another field” feature to first filter by county before selecting the customer name from the drop-down menu.  Although adding the record directly from the parent record would be more ideal, searching by county is much more convenient for our sales reps while they're out in the field.

I tested everything below in a copy of the app, and I think it will work.  That being said, I don’t want it to seem like it’s working and then find out 3 weeks later that everything behind the scenes is falling apart.


The Solution:

1) Ensure no users are online during the update time period

2)      Change Key Field from Customer Name to Record ID

3)      Update all customers to fit naming conventions

          a.       Using Grid Edit, copy Customer Names to Excel

          b.       Use “Proper” function to change the names to lower case

          c.       Paste updated names to Grid Edit

4)      Change Key Field from Record ID to Customer Name

5)      Redefine “the values in this field depend on a selection in another field” for all related tables

6)      Cross my fingers, hope, and pray that nothing broke.

 

I really appreciate your input here.  Thank you for your help!
Photo of Caitlyn

Caitlyn

  • 506 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Pretty good but in my opinion the key feel to your customer table should remain as record ID. That way if there is a slight change to the customer name in the future everything will stay linked.

Why is it that you feel you need to change the subject to be the customer name as opposed to the record ID. My suggestion is to leave it out for record ID and just fix any conditional drop downs so that they work off the record ID.

Of course the other thing that goes without saying, but I will say it, is that you obviously make a copy of your after you make these changes. There is no cost for making a copy of the app.

You are probably right that the links to the records in the trail table which is in a cross application relationship was offering, but as you said there's only 10 records so you can link them back up manually.
Photo of Caitlyn

Caitlyn

  • 506 Points 500 badge 2x thumb
Thanks for your quick response, I was out of the office and didn't see this until today.  I'm definitely not against changing the key field to the record ID, but how can I do this and make sure the only thing that displays is the Customer Name on each form?  I also need it to link to the record from the customer name.

I know that I can update the record picker to show only the club name field, but in my test version after I save it, it displays the record ID.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
The best way is to edit the field properties for the Related Customer to have the proxy field be the Customer Name.  Then use that [Customer name] field on the on the form.
(Edited)
Photo of Caitlyn

Caitlyn

  • 506 Points 500 badge 2x thumb
Ok, it looks like that works well.  I really appreciate your help!
Photo of Caitlyn

Caitlyn

  • 506 Points 500 badge 2x thumb
Actually, it worked last night but isn't working this morning, and I didn't change anything.  Unless I can guarantee that it works 100% of the time, I can't risk changing it.  I think I will have to change the key field back to the customer name.  I appreciate the suggestion, regardless.