Jan 14, 2022 08:48 AM
I work for a community development corporation, and am developing a database to store information on businesses, individual properties, and residents/ partners/ misc. individuals. I have organized my base into three tables with the primary fields: Addresses, Business Name, and Contact name. The idea is that sometimes we want information on a property (state of repair, vacancy, etc), sometimes we need info about a business (sector, ownership demographic, etc), and other times we need info on specific people (if they are residents, touchpoints, contact info, etc.). We also want to know how these different sections interplay. For example, if I am looking up a property, I would also like to see what business is in it and who is the business owner and who is the property owner (if we have already collected that information).
Now, for my problem: I want to make it easy for people to add info to this base without having to update each table every time. For example, if I talked to a Jane Doe, a manager of XYZ Tattoo at 1111 N Way, I could go to the “Contacts” table, add Jane’s name and contact info, and select (or add) XYZ Tattoo from the “Business info” linked table field and 1111 N Way from the “Addresses” linked table field. However, when I tried this, the linked information is not updating across all tables. Specifically, while the info in Jane Doe’s record is correct, with the linked fields updated, when I go to the “Business Info” Jane Doe has not populated the linked record field “Contact Name”. To make it more confusing, she has populated the “Contact” field in the "Address record. See Screenshots.
I would like two outcomes, First, for the “contact” field to be updated on the related “business” and “address” records on their perspective tables when those records are selected on the linked record field in the contact table. Second, it would be ideal if, by associating the business and address within the contact record, the business and address would then be associated in their perspective tables even though I did not go in and associate those records in each of those tables. That way, I don’t jjhave to update multiple tables everytime I get new information
Any ideas? Thanks in advance!
Jan 15, 2022 03:50 AM
Just by seeing your images, to be able to get fields values from other tables you need:
It’s seems like you added new Linked Record fields instead of Lookups.
Jan 19, 2022 11:38 AM
Hi fellow Elias,
Thanks for the reply. I know I can do a lookup field, but that doesn’t get my desired result. Basically, if input an individuals contact info on the “resident/ partner” table, and select their address and business from the link fields within that “resident/ partner” table, I want the “contact name” to be updated on the “property/ address info” table as well as the “business/ organization info” table.
In short, I want the linked field to not just pull data from another table, but also to “push” data onto that other table in the appropriate field. Does that make sense?
Jan 24, 2022 01:22 PM
Now I’ve read the full post, and think my answer was not so far from the problem :grinning_face_with_sweat:
You have to connect every type of information by their relation. A Business is inside a Property? Reflect that. A Person is owner of a Business? Reflect that. A Person lives in a Property? You know.
Then, you can add as many Lookup fields as you want to “carry on” the information from one table to another (and then to another one). Let’s say a Person has a Business that is inside a Property:
You can add extra Lookup fields on Business to get address, city, building height, or whatever data you want, and then show it in Persons with the corresponding Lookups.
I think I didn’t understand your full case, but I hope that this is what you need and will be able to apply it.
Jan 24, 2022 01:32 PM
Reading again, I see you relate each Table with the other two, but that is not correct. It’s logical that if you edit one relation, the other does not updates. Because is ANOTHER different link.
Also, I don’t know how have you setup the links, but this is strange:
Jan 24, 2022 01:40 PM
I was making this little schema, and just realized I could not name the third relation because you described just 2 relations (as I suggest), but did 3 in your base :grinning_face_with_sweat: