Need Key Values Suggestions and data adjustments on Spreadsheet with values inserted

Questions
,
#1

Hi guys,

I’m trying to adjust an existing Spreadsheet data to create an app. From this Spreadsheet there is a costumer registration (Companies and People) sheet that need a costumer nickname, address, representative, phone, email. Sometimes we have multiple rows from a same company but different emails, phones and representatives such as bellow

Razão Social = Legal Name Referência = Nickname CNPJ/CPF = Legal ID Inscrição Municipal = District Document ID Inscrição Estadual = State Document ID UF Cidade = City Endereço = Address Bairro = Neighborhood CEP = Brazzilian Address ID Endereço Operacional = Operational Address Telefone = Phone Setor = Company department Responsáveis = Representatives Email
Beda Lab Official Name BedaLab xxxxxxx UF Bedalab’s City Bedalab Address Bedalab’s Neighborhood 28035-280 (22)2737-1510 Administrativo Leila leilaoncobeda@yahoo.com.br
Beda Lab Official Name BedaLab xxxxxxx UF Bedalab’s City Bedalab Address Bedalab’s Neighborhood 28035-280 (22)2737-1510 Administrativo Lais direcao.adm@imne.com.br
Beda Lab Official Name BedaLab xxxxxxx UF Bedalab’s City Bedalab Address Bedalab’s Neighborhood 28035-280 (22)2726-9090 Técnico Rafael rafaelrandolfo@gmail.com
  • What should be my key value? I’ve read an article about keys (link on the end of the Topic) but i don’t know what should be a good choice of key value since my goal is to track each costumer by it’s nickname and then see all emails, phones and representatives for this costumer.

Also we track payments by a tax document that is a tax invoice for products solds and tax receipt for maintenance made. But sometimes our costumer ask to prorate the value. So on the Spreadsheet we add a row with the same values of order ID, Quotations, Tax Document , Costumer Name, Tax Document Value, Rates, Payment Value, Payment method, Payment Document Number. And the due is add by 30 days on the second value. Then we register each payment on each due dates such as bellow (our dates are set as dd/mm/yy)

Order ID Quotations Tax Document Costumer Name Tax Document Value Rates Payment Value Payment method Payment Document Number Due Paid Value Date of Paymant
RC200433 CP2011168 , CP2011164 NFSe 802 2.750,00 2 1.375,00 Boleto 0001 05/01/20 1.375,00 03/01/20
RC200433 CP2011168 , CP2011164 NFSe 802 2.750,00 2 1.375,00 Boleto 0002 05/02/20 1.375,00 05/02/20

Notice that those 2 rows reference a one payment, that is from order RC200433. But the Order ID shouldn’t be de key value because some times the payment refers more then one order. In this case what would u guys recommend to be a key value

  • Further if u guys have some tips to do better adjustments on my existing data values (without losing then obviously) i’d appreciate

Hope i explained it well, my English is getting rusty :sweat_smile:
Graciously,
Thales

#2

Is one of these columns having UNIQUE values?

CNPJ/CPF = Legal ID Inscrição Municipal = District Document ID Inscrição Estadual = State Document ID
#3

No, they are the same

#4

Sorry, you are correct because they are all the same contacts. I believe the most appropriate approach can be creating a Companies table first, listing all the companies with appropriate Key column values. Then, add 2 columns to this Customer Registration table:

Column Name Column Type Initial Value Ref Table isPartOf
Registration_ID Text =UNIQUEID() — —
Company_ID Ref — Companies ON

For how to create UNIQUEIDs for the existing records of this Customer Registration table, check this page:

For the [Company_ID] column values, copy&paste associated Company’s key column value for each record on this table.

With this you will establish a reverse reference between the Companies table and the Customer Registration table, where you can create multiple Customer Registration record for any company.

#5

Thank you. It’s working. Regarding the other table (payments) should i proceed the same way?

#6

You’re welcome. Thinking that companies may have multiple payment records, then yes.

#7

Yeah i see but when i have 2 registrations that mean 1 full payment. I mean when a costumer wants to pay in 2 installments on the Spreadsheet we create a row with the same values expect Payment Value, payment document number, Due, Paid value and Date of Payment columns. For example if you need to pay $100 and ask on 2 installments we simply do:

Costumer Name Order ID Order Value Rates Payment Value Payment Date
LeventK N1 100 2 $50,00 12/08/20
LeventK N1 100 2 $50,00 01/08/21

So here u paid the order N1 that costs $100 but u paid $50 on December 2020 and the $50 pending you paid one month later.

In this case doing a Reverse Reference would be a good method?

#8

Rather than the customer table itself, I believe it might be better to reverse ref partial/installment based payments with the Order. So it will look like this:

Payment ID Order ID Custumer Name Order Value Rates Payment Value Payment Date
PID001 N1 LeventK 100 2 $50,00 12/08/20
PID002 N1 LeventK 100 2 $50,00 01/08/21

This way, for each Customer Order, you can create multiple payment records.

#9

I’ll give it a try. But i’ll check the question earlier as solved, thank u.
Best,
Thales

1 Like

Company

Platform

Pricing

Solutions

Support

Account

Built in Seattle, WA | Copyright 2019