How to design a relationship between customers, customer payments, and billing periods

[Sean_Comerford]

New Airtable user, I feel I am swiftly adapting to working in this environment. However, one issue I am having trouble visualizing is how to lay out a series of tables to meet this particular goal; any suggestions as to the most organized way to approach this are welcome.

What I am looking to do may be able to be tackled in numerous ways. However, I am curious to see what the best practice solution would be as someone new to Airtable.

Existing tables:

• Clients (contains general customer information, such as Company Name, Status, Start Date, End Date, etc.)
• Client Payments (a small table containing form responses. Values include Company Name, Service Period (month/year), and Payment Date. When a client bill has been processed, the form is completed, and a row containing the company name, service period, and the payment date is captured on the Client Payments table)
• Billing Periods (contains a list of Service Periods, which are monthly periods stored as dates (i.e., 1/1/2020) starting on the first of every month going through December 2049.

The goal:
For each Company Name on the Clients sheet, I require a way to match a date value from the Client Payments table and associate it with the relevant Service Periods that fell between the client’s Start Date and End Date. The result will make it so that the viewer can see which service period a client received service in and whether or not they completed payment for each service period month.

My first thought was that a junction table connecting Clients and Billing Periods would be needed, with a Service Period/Client combination for every possible combination of client and month, but with many clients having not been active for many of the service periods, this solution will create many rows of blank relationships that are not/will likely never be used. I’m curious to see if there is a different approach I haven’t considered. Any insights would be appreciated.

[Ashraf_Madkour]

hi
create table for clients with
client name & ID

create a project table for every client has a more than one project
add client name ( field as a linked field with a client table )

same as above for a deliverables for every projects linked with project table
add project name field as a linked field with the project table

in the main client table create a lookup field with the 2 other tables

that way my tables is connected

[TheTimeSavingCo]

Hey Sean, what about an automation that runs when a new record gets created in the Client Payments table that looks for records in the Billing Periods table that are for that company and match that billing period, and then links them together?

I think I don’t fully understand what Billing Periods looks like though, and was hoping I could get a screenshot of some example data?

[Sean_Comerford]

Hey Adam, I can provide some basic information if it helps visualize:

The Billing Periods table contains one Autonumber RowID field (ie: “1”, “2”, etc.), a Billing Period Date field (ie: “1/1/2020”), and a Billing Period Pretty Name field (ie: “January 2020”).

The Client Payments table may be what you are thinking of, which contains the data for each client payment: Client Name (ie: “ABC Furniture”), Payment Date (ie: “1/1/2020”), Billing Period (“January 2020”).

Ideally, a table will exist to capture not only the billing periods that a payment on behalf of a client was made in, but all billing periods that occur between the client’s Start Date and End Date (this is helpful so that billing periods in which no payment was made can be reported on rather than just the billing periods in which a payment was made). I am hoping to conceive of a way to achieve this that doesn’t involve creating an unruly number of Billing Period fields or rows per client.

[TheTimeSavingCo]

Thanks Sean. Hmm, what do you think of having another table called Contracts or some such that’s linked to the Clients table? The Contracts table would contain the Start Date and End Date of each contract

With this, we could set up an automation that would allow us to link each Client Payment record to a specific contract, based on whether the Client Payment record’s linked Billing Period record is within the contract’s Start Date and End Date value

This would then allow you to group records in the Client Payments table by contract, which would show you all of the Client Payments records, grouped by the Start and End date like you want

===

I have to say though, I still don’t think I fully understand your setup or what you want the output to be, so I do apologize if I’m way off the mark here