Need help linking (or understanding?)

[lotarm1949]

I am a new TF user, but I have forgotten almost everything I ever learned about SQL and Access (about 20 years ago!).

I have a TF Form (“database”?) for Clients. This contains records for three clients, Adam, Barbara, Colin — their name, plus other data such as address, etc.

I have another TF Form for Client Tasks. This contains records for the tasks I have to do for any given client, plus other data such as due date, estimated time, etc. For Adam, I have to paint his house; for Barbara, I have to fix her car; for Colin, I have to design him a database . . .

I would expect to have Form Clients designed (in part) as field client_name = type Text, and the Form is populated with Adam’ s, Barbara’s and Colin’s names.

I would expect to have Form Client Tasks designed (in part) as field client_name = type Link-to-Form Clients, so that the task “paint house” is assigned to client name “Adam” linked from Adam’s record in the Client’s Form, etc.

Yet when I am creating the “paint house” record in the Client Tasks Form, and I want to link the Client Name field to Adam’s record in the Clients Form, TF tells me that the Clients Form has no records in it and wants to create a record!?

What am I doing wrong?

Lotarm1949

[Brendan]

Hi Lotarm1949,

You will need to use a Many to Many link from Client Tasks to Clients. Then you’ll see a checkmark button that will let you pick from a list of clients to link to your Client Task form.

However, I would not design it like this. You should add a One to Many Link to Form field to your Client form that links to your Client Tasks form.

Then you start at the client and add tasks to them. The tasks will be restricted to just the selected client.

You can also use the “Hide Form” checkbox option on the Edit Form screen for the Client Task form to hide that form from your main Forms list. That way you can just focus your attention on your clients and then see what tasks you have for them.

At the moment you can’t see the inverse relationship from Client Task to Clients, but I plan to add that ability to a future update.

Thanks!

Brendan

[lotarm1949]

Thank you Brendan

Interestingly, many-to-many from Tasks to Clients doesn’t work? — but your recommended approach does indeed. Except I would have expected that, when opening the Tasks form from the Clients form the client name would already be entered n the Client Name field? This way, when I start from Client Adam, add a task, I have to enter Adam’s name again in the appropriate field of the Tasks form. Seems sort of redundant?

Anyway, I’m up & running with what I needed, which is a task list of outstanding tasks, by client, with ability to filter, sort, etc.

Thanks. Lotar.

[EddieCG]

I am also having trouble understanding how Linked Forms work (I have a lot of experience programming relational databases in MS Access).

I have my Parent form, 2 fields; Name and Link to (my child form).

I enter John in my Parent form, and then in the Link to field, which opens my Child form, I enter the children’s names, Albert and Bertha. My Child form now has 2 records, which show up in John’s record on the Link to field.

John has a wife, Delia, who is the mother of the same two children! But if I add Delia to the Parent form, and go to the Link to field,there are no records! And if I add Albert and Bertha’s names, and go to the Child form, I now have two records for Albert and two records for Bertha.

I am obviously not understanding something somewhere, and don’t know where to get help. Help!

[Brendan]

If you need to share multiple child records with more than one parent record, then you need to use the Many to Many link type. Then you’ll be able to select from your list of child records to link to the parent record.

Thanks!

Brendan

[Author]

Posts