Basic topic. Silly question?

Hi friends!

I have a doubt:

Suppose In have a Writters sheet tab and a Books sheet tab. Both with a relation based in AuthorName column which is obviously in both tabs.

Okey.

Now from a Glide App I would like to change the AuthorName and being automatically updated in all relationed Book items of Books sheet tab as I don’t want to loose that previous relation.

This is the normal behavior on any relational data base. Glide had a different logic.

Is it possible to achieve this in Glide?

Thanks

1 Like

So at the moment you are probably using the “Author’s name” to create a relation and hence it’s breaking.

I would recommend using a Unique ID (Maybe a row ID) to create the relations

Or maybe give a serial number to each author and use that to create a relation. Then you could set up a Lookup in your books tab to bring in the author’s name. This way whenever you edit the author’s name in the “writer” sheet, it won’t break a relation and will automatically update on the “books” sheet as well.

4 Likes

:blush: Many thanks @Manan_Mehta

Ok that makes all the sense and it’s the usual procedure for relational databases, but it only would work in new items created using a choose component which retrieve the IdRow from Author and keep it in the IdAuthorRow which I use in Books to make them related. Of course now I can change name of the author and it upgrade as a lookup column in Books. Perfect!

But…

What happens when I’m trying to relate hundreds or maybe thousands of Authors & Books which I only have that coincident data?

Do I have to do it manually? Or maybe copying and paste making sure they are in the same order… and then stabling that new relation?

know what I mean?

Any idea about this procedure?

I have tried this and don’t work:

  1. Create a lookup column that retrieves the AuthorIdRow and create a new relation based in this.

  2. As the AuthorName is which permit the AuthorIdRow retrieve when I delete the AuthorName column in google sheet the AuthorIdRow look up column disappears and also the IdRow Relation…

I’m lost.

Ok, let me tell you my point of view if I understand your trouble…
As you wrote, it’s a typical problem in databases and I think I know what your problem is about.

It’s like when you log a book’s price in your Sales DB dynamically (ID or reference of price is written but not the real value), if you change some day the book price, this new price will affect your Sales DB data, your old registers will be updated and your accounting will be a mess!

In this case, the best solution is logged/saved the real value (price) and not the relation or reference.

Feliz día!

1 Like

I went through this because I wanted to convert to using ID’s for all of my relations because of the same problem with renaming names. My case isn’t books and authors, but I’ll refer to what I did like it is books and authors

  • What I did first was add a RowID column to the authors sheet
  • Next within the Books sheet in Google Sheets, I create a temporary column that used an arrayformula of VLOOKUP to take the author name in the book sheet and use it to lookup the matching author name in the Authors sheet and return the RowID.
  • Then I created a new AuthorID column to permanently hold the author id’s and copied all of the VLOOKUP values into the new authorid column.
  • Then you can delete the temporary arrayrformula column.
  • Once that is done, then you have the AuthorID permanently in your Books sheet and you can begin to remove the AuthorName column from the Books sheet.
2 Likes

That sounds perfect :ok_hand:@Jeff_Hager

I’ll try but I’m absolutely convinced, that procedure will work!

Smart!

Could you be so kind to help me with the syntax of that temporary VLOOKUP ArrayColumn?

An example of this would help me a lot…

I’m not using Authors & Books also but it is simple to understand with this example… :wink:

Thanks & thanks again

2 Likes

Thanks @gvalero.

When you are using updatable prices in a PRODUCTS sheet, they must be registered as independent and static in the SALES sheet,

That way if you change the original product price it won’t affect your sales results as they remain static.

Saludos!

1 Like

Put this formula in row 2 somewhere in your ‘Books’ sheet:

=ARRAYFORMULA(VLOOKUP(B2:B, 'Authors'!D2:Z, 3))

  • B2:B would be the column that contains the ‘Author Name’ in the ‘Books’ sheet
  • Authors!D2:Z specifies the column that contains the author name in the Authors sheet. Setting the range to column Z allows us to pull back any column after column D (Author Name).
  • Set the last parameter (3) to the number of columns after the Author Name column, where the RowID you created would be located. (VLOOKUP can only return values to the right of the column you are searching, so make sure RowID is after Author Name).

(Some countries use semi-colons (;) instead of commas (,) in google sheet formulas, so you may or may not have to swap them out in the formula.

3 Likes

Thanks so much again @Jeff_Hager

I’ll do my best and will report the results but it sounds great!,

Details of the explanation are superb!
:smiley:

1 Like

Brilliant!!

It worked smooth and fun…

Thanks so much for your help.

I will do this procedure always, as a good practice, at the beginning of data structure when I have eventually editable related fields… as in any other DB.

:100:

2 Likes

@darder even being in Portuguese, I have posted about a personal project of mine Jungle

I made similar as Jeff explained to you here up, where I pick not only autor name, but also avatar.

In this app I have also a notification tab where I also used this feature to get in which Seed (topic name inside the app), its title, its cover, and so…

Here’s the topic if you wanna take a look

1 Like

Thanks @Lucas_Pires Lucas… love it!

1 Like

When it gets done, share with me! I wanna see it :grin: