Base relationships

[sowles]

New to LibreOffice Base (and all the tools). I’ve used Access in the past (like 20 yrs ago) so i’m reaching out for a bit of help.

My task, set up a database that keeps all our customer information and allows us to track our customer interactions. This will be a key tool to 1 - house all our customer info for use when needed and 2 - to see which customers have not been contacted recently (or had an interaction) so we can reach out to them to make sure all is well.

I know I need at least 2 tables, one for customers and one for interactions. I’m thinking through the relationships and know that there will be zero or multiple interactions per customer with one customer for each interaction. This tells me Customers is my primary table with Interactions being a secondary table (am I correct?) With relationships I believe I can add fields from my customers table and link them to fields in the interaction table. I’m not quite sure how to make sure that the info for all 5 fields copies correctly, do I need to use the customer primary key field and just update that one so the rest update accordingly or is there another way? Also when I have all my data updated and have been adding interactions as they happen, is there a report that will show all of my customers (with or without interactions) so I can quickly see who has not had contact in the last 3 months? Will this report (or view) be able to show data from both the customer and interaction tables or do I need all the info I want to see in one table?

I’ll start there and see where this goes. Thanks for your help, please remember all this database stuff is going to be fairly new to me as is this tool.

Rob

[Ratslinger]

Hello,

Please note this site is a question and answer site. Asking multiple questions in one post makes it difficult for others for find answers. Please keep questions to one per post. Thank you.

For future reference, it is always helpful to include your OS, the specific LO version you are using and with Base the database used.

[sowles]

Thanks, I’ll try to remember to create separate questions for now on. Also, no LO version yet, I’m being told that LO is what will be used but the IT guy hasn’t loaded it yet. Just been reading and watching videos and setting up my ideas so I’ll be ready to jump in once I have it.

[Ratslinger]

Hello,

For what you describe, you need two tables as you stated. The connection is simple the primary key of the customer is placed in a field of the interaction table. Here is a sample file from another post I answered:

Sample ----- Students.odb

It contains Student table (your customers) and their degrees applied (your interactions). It also has a Degrees table which is used in a list box for selection in the degrees applied sub form.

As for a “report” based upon no contact in last X period, this can be done with a query limiting the records returned. The results can be shown on a form or a report. This can show whatever data you have stored.

For reference, see the documentation → LibreOffice Base Handbook

[sowles]

Thank you! Glad to see I’m on the right track. Once I have the software and am getting into the forms and reports I’ll see if I have any more questions.