One-to-Many Relationship

[bbaliner]

Hey all,

I think that the inherent problem here lies with One-to-Many relationship. Let me explain.

I have two files:

1. Company => one record per each company

2. Employees => several records per each company

These two are connected on CompanyNumber, a classis One-to-Many. I want to be able to view how many employees there are in each company (just a number) from Company file.

Fairly simple, I use Summary fields and then break the Total field in FamilyNumber in Employees file, and then I can drag it on the layout in Company file. But for this GetSummary field to work, the user must first sort the Employees file on FamilyNumber.

This wouldn't be a problem, accept users don't want to "remember" to click the button and sort the Employees file (I spoiled my users). So what do I do?

The whole idea behind this is to see the employee number on the fly, meaning if someone is adding an employee right now in Employees file, I'll immidiately see it in Company file. I could write a scipt to set the field in Company, but they'd still have to click a button, AND it wouldn't be dynamic.

Any suggestions there, good people?

Thank you so much

[The Bridge]

Create an unstored calculation field in your Company database:

Employee_Count=Count(CompanyNumber::Employee_Name)

I don't know the exact names of the fields or relationships you are using, but I think this should give you the right idea.

[bbaliner]

Thanks so much, Bridge.

The answer was staring me in ther face the whole time, and I had mental constipation.

Thanks again, have a great holiday

[The Bridge]

quote:

---

Originally posted by BikeBoy:

Bridge,

Same scenario as above with Company and Employees, but say I have an Index number for the employee that was hired first. Because of One-to-Many, if the emploee is not the first emploee record in this Company, the index value will not show up.

---

Have your relationship sort (descending) by the first_employee_index field.

quote:

---

I am currently doing it same way as above, by totaling all the first employed employees, and then breaking them by company with GetSummary function. This puts the correct employee number in ALL of the company records, and so that's good for me. But I still need to sort the Employees file on Coimpany every time.

---

This seems overly complicated to me, but then again I'm not sure what you want to achieve here. If you want to find just the first-employed employee for any given company, create a concatenated primary key in your Company database such as Company_ID & "1". In your Employee database create a concatenated foreign key such as Company_ID & NumToText(not IsEmpty(First_Employee_Index)). Then create a relationship from Company db to Employee db matching those two fields.

Having said all that, by adding the sort order (described above) to your original relationship, the Company database will treat the employee with a value in the first-employed index field as the first related record.

[bbaliner]

Bridge,

Same scenario as above with Company and Employees, but say I have an Index number for the employee that was hired first. Because of One-to-Many, if the emploee is not the first emploee record in this Company, the index value will not show up.

I am currently doing it same way as above, by totaling all the first employed employees, and then breaking them by company with GetSummary function. This puts the correct employee number in ALL of the company records, and so that's good for me. But I still need to sort the Employees file on Coimpany every time.

Again, of coarse I can write a script to set the field in the Company file, but is there a way to do this with Caculated fields?

Many thanks

[bbaliner]

Brilliant!

Thank you

[The Bridge]

My pleasure!