Selecting 1 Phone Number Out Of 3 Choices

[kpcollier]

I have a Client Table that stores all of our clients information. This holds information such as Name, Phone Numbers, Address, Email Address, etc. Pretty self explanatory.

I also have a table called Work Order Table. We use this table to store information for Work Orders - when our customer needs some service done, we fill out a Work Order Form with their information and details about what work needs to be done.

The problem is the phone numbers. For each Client in the Client Table, there is Home Phone, Cell Phone, and Work Phone fields. Some clients may only have one or two of those phone number fields filled out, leaving the other(s) blank. To be able to tell which Work Order belongs to which Customer, we include Customer Name, Address, Phone Number, and Email in the Work Order Form.

To make this easier for my employees, the Customer Name field on the Work Order Table is autofill - you start typing in a name, it searches that in the Client Table, and when there is a match it autofills the Customer Name. From here, I can use cascading elements for Address, Email, etc. to automatically fill those fields in the Work Order Form.

However, this creates a problem because of the way the phone numbers in the Client Table are set up. I can only have the cascading value be ONE of those Phone Number fields, and that field may or may not be one of the empty ones. I have tried using a formula field for this - but that makes it so the phone number cannot be edited whatsoever if there is ever a need to.

 

A lot to go over - but its the last part of this app before it is finished, and it is killing me. I think I may need to change my workflow, or maybe how the Client Table is set up... Hoping someone has some suggestions.

[kpcollier]

I didn't necessarily find a solution to the problem how it was above, but I decided to just do an overhaul of my Client Tables and all relating datapages to make it work a different way.

Instead of having fields in the Client_Table for Home_Phone, Cell_Phone, and Work_Phone per client, I changed the fields to Phone_1, Phone_2, and Phone_3. I then added 3 new fields to my Client_Table called Phone_1_Type, etc. The purpose now is to have the first phone number field, Phone_1, to always be populated if there is a phone number. Then with the newly added fields, I express what type of phone that is: cell, home, or work. Now the cascading field on the Work Order Form takes in the first phone field, Phone_1, which should be populated no matter what type it is. 

Hope this helps someone in the future.