Working with two tables

Question

I have two tables which The following lists show the table names in Bold and then some of of the fields in the tables listed below the table names. I am trying to ensure that a ticket entered in the&nbsp;Tickets&nbsp;&nbsp;table to record work being done is a valid ticket that has been shipped to a location in the&nbsp;Field Ticket Stock Table&nbsp;before the ticket can be saved in the&nbsp;Tickets&nbsp;table. I have been trying variations of the table to table relationships so I can try to do a search of the Field Ticket Stock Table to see if the Ticket # from the&nbsp;&nbsp;Tickets&nbsp;table exists otherwise the user will be given a message that the Ticket #&nbsp;cannot be used. I have been unable to make this happen and am looking for suggestions on how to make it work.&nbsp;Field Ticket Stock Table&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TicketsT#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Ticket #FT#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Field Ticket #Type of Work&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Location&nbsp; &nbsp;Date Shipped to Location&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;TOWLocation&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LocationWho Shipped.​​&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Date of Service&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;(this table has 233 fields)​​​------------------------------Paul Guillory------------------------------

markshnier__you · Answer

Can you tell us the Key field of each table?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

paulguillory1 · Answer

&nbsp;The key field for the Tickets table is Record ID # and for the Field Ticket Stock table is Record ID #.Sincerely,Paul S. Guillory II Standard Energy Services is a Platinum Safety Award winning company.&nbsp;IT ManagerStandard Energy ServicesPO Box 2724Lubbock, TX 79408Voice: 806-741-1080Fax: 806-741-1301 CONFIDENTIALITY NOTICE: The information contained in this ELECTRONIC MAIL transmission is privileged and confidential, intended only for the use of the individual or entity it is addressed to. It may also be subject to the attorney-client privilege or be privileged work product or proprietary information. If you are not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication (other than to the addressee(s)), is strictly prohibited.&nbsp;    	 		 			 				 			 		 		 			 				This email has been checked for viruses by AVG antivirus software. 				www.avg.com

markshnier__you · Answer

If you change the Key field of the Field Tickets table to the T#, then you can make a relationship where 1 Field Ticket has Many Tickets based on the reference field of the Ticket # in the Tickets table. The user can then be made to choose the field ticket # and that can be a required field. So they must find the correct Field ticket before saving.

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

paulguillory1 · Answer

The T #&nbsp;field is a text Formula field. There is also a little background information might be helpful in understanding why I am trying to use the search method instead of having users simply select the correct Field Ticket Number. We still use paper Field Tickets to record work we have done for our customers because of customer requirements. We have been using a quickbase app for managing the Tickets once they are created for 18 months. The app was designed without tracking what Field tickets were shipped out to the remote office locations. We have found unused field tickets located in drawers or various other locations. Worse yet we have completed Field Tickets that were laying around that had not been entered into the QB Ticket Tracking app where they should have been ​recorded.I have attached a document describing the work flow of a Field Ticket in our organization which was created after finding we had problems losing Field Tickets. The process documented describes the process flow using an Excel workbook with each remote office having their own workbook.&nbsp; Now that we have discovered the whole in our design for the QB app I am trying add the necessary process for tracking the Field Tickets from the the receipt of the new field ticket from the people we have print them until they are shipped to the remote yard.------------------------------Paul Guillory------------------------------

markshnier__you · Answer

It's beyond the scope of what I can do on this forum to read that document and digest it.&nbsp; We could work one on one if you like though on a contract basis.&nbsp; Contact me via my email address.One though is to set up a Connected Sync table to Sync across the Field Tickets every hour and set the Key field there to be the T#.&nbsp; Then make that relationship I was talking about.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Working with two tables

Related Content

Address field not working

Maximum Summary field not working

Moving Fields from One Table to Another

How to relate two tables with the key to a third table?

Table is missing