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
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?
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?
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.
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.
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