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

[ATR]

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
Graciously,
Thales

help.appsheet.com

What is a Key?

Learn the purpose of keys and why they’re necessary.

[LeventK]

Is one of these columns having UNIQUE values?

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

[ATR]

No, they are the same

[LeventK · Accepted solution]

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:

help.appsheet.com

Manually Generating UNIQUEID() Key Values

How to manually generate UNIQUEID() key values.

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.

[ATR]

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

[LeventK]

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

[ATR]

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?

[LeventK]

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.

[ATR]

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