Different Field Reference depending on value

Question

&nbsp; &nbsp; The formula below works to list top 5 customers input in a table field into a form field I need but at the end it says "No GC's have been identified for this Project") when the table is empty. &nbsp; I need it to first reference the field called "Awarded Contractor" or the "JOB Number" fields. If either of these fields has a value then the only value in the Bidding GC field should be the Awarded Contractor field value. &nbsp; &nbsp; Any ideas on what should be changer in the formula to reference these fields? &nbsp; Thanks &nbsp; &nbsp; &nbsp; If( &nbsp;&nbsp;&nbsp; Part(ToText([All GC]),5,";")&lt;&gt;"",  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),3,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),4,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),5,";")&amp;"...", &nbsp; &nbsp;&nbsp;&nbsp; Part(ToText([All GC]),4,";")&lt;&gt;"",  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),3,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),4,";"), &nbsp; &nbsp;&nbsp; Part(ToText([All GC]),3,";")&lt;&gt;"",  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";")&amp;" | " &amp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),3,";"), &nbsp; &nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";")&lt;&gt;"",  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";")&amp;" | "&amp;  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";"), &nbsp; &nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";")&lt;&gt;"",  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),1,";"), &nbsp; "No GCs have been identified for this Project") &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Michael L. Hall | Excavation |&nbsp; Survey Mgr | RPM xConstruction, LLC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (O) 214-239-4722 - Ext. 4926 | (M) 972-837-3350 | www.rpmxconstruction.com &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; planroom@rpmxconstruction.com | mhall@rpmxconstruction.com | surveying@rpmxconstruction.com  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5208 Tennyson Parkway | Suite 130 | Plano, TX&nbsp; 75024&nbsp;&nbsp; cleanemail*** &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONFIDENTIALITY NOTICE - The information contained in this transmission, including attachments, may be (1) subject to the Attorney-Client Privilege, (2)  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attorney work product, or (3) strictly confidential. If you are not the intended recipient of this message, you are notified that you may not disclose, print,  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy or disseminate this information. If you have received this transmission in error, please reply and notify the sender (only) and delete the message.  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Unauthorized interception of this e-mail may be a violation of criminal law.  &nbsp;    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

blakeharrison · Answer

Michael -Is your intent to convert the [All GC] field to text and list out the first 5 values, separated by "|", unless there is a value in the [Awarded Contractor] or [Job Number] fields, then show that field?If so, then I would suggest massaging the original formula a bit and use something along these lines:
Case(True,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Awarded Contractor]&lt;&gt;"",[Awarded Contractor],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Job Number]&lt;&gt;"",[Job Number,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; List(" | ",Part(ToText([All GC]),1,";"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Part(ToText([All GC]),2,";"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Part(ToText([All GC]),3,";"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Part(ToText([All GC]),4,";"),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Part(ToText([All GC]),5,";")))------------------------------Blake Harrison - DataBlender.ioQuick Base Solution Provider------------------------------

michaelhall · Answer

It seems to want to give me an error. Its wanting a bracket or another comma but neither are working for me. &nbsp; Case(True, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Awarded Contractor]&lt;&gt;"",[Awarded Contractor], &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Rpmx Job Number]&lt;&gt;"",[Rpmx Job Number, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; List(" | ",Part(ToText([All GC]),1,";"), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),2,";"), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),3,";"), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),4,";"), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Part(ToText([All GC]),5,";"))) &nbsp; &nbsp; &nbsp; &nbsp;     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Michael L. Hall | Excavation |&nbsp; Survey Mgr | RPM xConstruction, LLC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (O) 214-239-4722 - Ext. 4926 | (M) 972-837-3350 | www.rpmxconstruction.com &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; planroom@rpmxconstruction.com | mhall@rpmxconstruction.com | surveying@rpmxconstruction.com  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5208 Tennyson Parkway | Suite 130 | Plano, TX&nbsp; 75024&nbsp;&nbsp; cleanemail*** &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONFIDENTIALITY NOTICE - The information contained in this transmission, including attachments, may be (1) subject to the Attorney-Client Privilege, (2)  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attorney work product, or (3) strictly confidential. If you are not the intended recipient of this message, you are notified that you may not disclose, print,  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy or disseminate this information. If you have received this transmission in error, please reply and notify the sender (only) and delete the message.  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Unauthorized interception of this e-mail may be a violation of criminal law.  &nbsp;    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

michaelhall · Answer

&nbsp; &nbsp; &nbsp; &nbsp;     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;   &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Michael L. Hall | Excavation |&nbsp; Survey Mgr | RPM xConstruction, LLC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (O) 214-239-4722 - Ext. 4926 | (M) 972-837-3350 | www.rpmxconstruction.com &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; planroom@rpmxconstruction.com | mhall@rpmxconstruction.com | surveying@rpmxconstruction.com  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5208 Tennyson Parkway | Suite 130 | Plano, TX&nbsp; 75024&nbsp;&nbsp; cleanemail*** &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CONFIDENTIALITY NOTICE - The information contained in this transmission, including attachments, may be (1) subject to the Attorney-Client Privilege, (2)  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; attorney work product, or (3) strictly confidential. If you are not the intended recipient of this message, you are notified that you may not disclose, print,  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; copy or disseminate this information. If you have received this transmission in error, please reply and notify the sender (only) and delete the message.  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Unauthorized interception of this e-mail may be a violation of criminal law.  &nbsp;    &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

blakeharrison · Answer

You'll need to complete the bracket for that field

------------------------------Blake Harrison - DataBlender.ioQuick Base Solution Provider------------------------------

Forum Discussion

Different Field Reference depending on value

Related Content

Same reference field, different selections within the form

Reference tables quandary

Tracking References and Definitions

Add button to create record in different app

Reference Numbers Displaying Instead of Names in QuickBase