Recording multiple messages and payments

[Dave_Harrison]

Hi everyone!

I’ve searched this forum and watched the help videos a number of times, but it’s just not answering my question in a way that enables me to understand as I would like.

Essentially, I have a table that has people register their interest with our product (organic veg) and they fill in a range of fields via our website.
A few zapier steps later and they are in our Airtable.
We take their name, phone number, address and a range of other things as well.

I have airtable sms the user a welcome once we move their status to customer.

The problem I have is, they reply and I write the record to their name but it is obviously overwritten each time.

I know I need a messages table, but don’t quite get how I should link and display that when looking at a customer.

They also have reoccurring payments that I’d like to write to a payment table also, but the same logic applies.

Can someone please help this make sense for me?

Thanks!

Dave.

[Jeremy_Oglesby]

I may be misunderstanding your question, but it sounds like all you are after is linked records.

You have a “Messages” table, and in that table you have a field that is a “Link to Another Record” from the “Customers” table (or whatever you have it named). So each “Message” will be linked to one customer, but each “Customer” may have many messages linked to them.

Same goes for payments - link it to a customer, so that each payment belongs to one customer, but each customer may have many payments.

[Dave_Harrison]

Thanks. That’s correct.
But do I need to, say, duplicate the phone number field in the messages to link it to the customer as a unique identifier or am I missing the point?

[Dave_Harrison]

I have the linked fields, but then how do I write to the messages table so they all appear under the customers table?
This is driving me a bit nuts!

[Jeremy_Oglesby]

It’s tough to advise without fully understanding what you’re trying to do.

I think what you want is to create a new “Message” record from within the “Customers” table.

Regarding the phone number, I think you just need the phone number to be tied to a customer – then you can pull it into the “Messages” table through a Lookup field.

I’m sorry, @Dave_Harrison, if this is not helpful. It’s hard to tell if I’m hitting the mark for what you’re looking for.

[Dave_Harrison]

Thanks, heaps @Jeremy_Oglesby.
I get where you are coming from, to an extent - but I’ll try and explain in more detail to try and get my point across.

On our website, we have people that are registering their interest in joining our program.
You can see the form they fill in here: https://www.parkigrocer.com/register-your-interest

Each of these fields corresponds to an airtable field on our ‘registered interest’ table. This table, essentially, contains all the customer information.
We take their phone number as part of this.

I have the SMS block setup to send messages to customers based on their mobile numbers.

They will also start making re-occurring payments each week for their fruit box.

When they make a payment or reply to a message, there is going to be a one (customer) to many (messages and payments) relationship.

Therefore, I need to set up a messages and payments table in addition to my ‘registered interest’ (customer) table.

My question really is, when I receive a payment or a message I need to write them to the ‘payment’ or ‘message’ table.

Using ‘messages’ as the example (as I presume payments will work the same way), when a message comes in I will write it to the messages table. In that table will be a messages field that contains the message.

So, now I have a ‘messages’ table full of records with messages received at different times and a ‘registered interest’ table full of customers.

Now, I’d like to pull up a customer’s details by expanding their record in the ‘registered interest’ table and within that detail screen see their entire history of messages that have been written to the ‘messages’ table.

My question is, what is the correct way to link these tables to get this functionality to work as I’ve explained, and what other detail do I need to write to the ‘messages’ table when a message is received in order to get this functionality? And I presume the same for payments?

Thank you for your help and patience!

[Jeremy_Oglesby]

If you are linking the records as I showed above, you will already be able to see all the messages for a given customer if you expand the customer’s record like this:

You can see in the “Messages” section there, that you can see all 3 messages I’ve made that are attached to “Billy”. I also created a “Rollup” field that you can see at the bottom there called “Messages Content”, which looks up the actual text of each message and joins them together into an array separated by commas. The setup for that field looks like this:

Does that help?

[Dave_Harrison]

That’s great @Jeremy_Oglesby. Ill give it a go today and then let you know how I go, if that’s ok?

[Dave_Harrison]

Hi @Jeremy_Oglesby - Thank you so much. I’ve figured it all out now thanks to you.
It was the primary key that is automatically created that was messing with my head.

Thank so so much.

All the best.