afbaer
asked on
Multiple field values in the same form
I have a "Work Shifts" table layout which is related to and accessed from my "Customer" table layout, casuing the customer name and ID number fields to be automatically populated by means of a script and a lookup. I must then select up to three contractors who will work on this shift in the Contractors fields, which are drop down boxes from a Contractors value list. My problem is updating the ContactorsID fields with up to three individual ID numbers. The aim is to also show the shift details in a portal on the "Contactors" layout via a ContractorsID reltionship between the "Contractors" and "Work Shifts" tables. In this way I can see a summary of what shifts each contractor works in the "Contractors" layout.
Obviously if I have three differet contractors I need three different ContractorID values on the same form, but if I have three diferent ContractorID fields (ContractorID1, ContractorID2 etc) then how can I make the relationship between the "Work Shifts" and the "Contractors" Tables?
Any help is much appreciated. I am reasonably new to all this and not at all versed in SQL, so I a hoping for a script based solution and an explanation that asummed beginner status.
Many thanks!
Tony
Obviously if I have three differet contractors I need three different ContractorID values on the same form, but if I have three diferent ContractorID fields (ContractorID1, ContractorID2 etc) then how can I make the relationship between the "Work Shifts" and the "Contractors" Tables?
Any help is much appreciated. I am reasonably new to all this and not at all versed in SQL, so I a hoping for a script based solution and an explanation that asummed beginner status.
Many thanks!
Tony
ASKER
Thanks, lesouef but I still don't quite get it. Showing you my table is a great idea but how do I do this?
can you post it on a web or ftp site?
ASKER
OK, you can get it at http://www.box.net/public/bufhm3bbcl. Go to "Cutomers" Layout and select "Shifts" . Select "+" button bottom right of screen to add a shift. The customer name and ID are automatically added. Now I need to be able to select two comperes to work this shift and have their individual ID numbers automatically appear. In this way their shift also appears in the "Comperes" layout "Shifts" portal.
Thanks!
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I thought I had understood yr problem, but I am just confused now that I saw your file, and even more with the example of jvaldes! or I am stupid, either...
First your file does not match the pb: what is a compere? the contractor?
then what do you need gamesshifts for?
jvaldes, how would you handle a 3rd, a 4th compere?
First your file does not match the pb: what is a compere? the contractor?
then what do you need gamesshifts for?
jvaldes, how would you handle a 3rd, a 4th compere?
See my mod of your file for a solution with a 3rd table to be able to cope with any amount of comperes.
The only non smart thing is the way to get the shifts compere_list updated in the shift. Actually this could be automatic if you'd use an event plug-in to update the list everytime a field is altered. So far, use th update button underneath the compere list in the shift layout
http://www.carina-tm.com/upload/Wild0.fp7
The only non smart thing is the way to get the shifts compere_list updated in the shift. Actually this could be automatic if you'd use an event plug-in to update the list everytime a field is altered. So far, use th update button underneath the compere list in the shift layout
http://www.carina-tm.com/upload/Wild0.fp7
it can only contain the contractor_ID and the shiftID.
everytime you schedule a contractor for a given shift, you should create a new record in this table.
this way you can view contractors for a given shift, and/or the shifts for a given contractor from within their respective layouts.
more help if you show me your file.