InfoPath Dev

I have a field @combine that I have set the default formula to reference two fields @PIC_Combined and @SIC_Combined based on the following formula:  This is a flight log form in which there can be multiple legs with different officers flying.  Most the time the same PIC and SIC will fly on all legs so I want the legs to default with the same names if the CrewSame is marked Y.  Regardless if the CrewSame is marked Y, the officers will be the same on the 1st leg of the flight; otherwise, their names wouldn't be listed as the PIC and SIC.    The officers can change after the 1st leg.  My formula is working great on the 1st leg and it works on the second leg as long as CrewSame is marked Y.  If the CrewSame is marked N, I want to be able to make a different selection and retain that information once the information has been submitted to the Main database and to SharePoint.  I can select another person and it will submit it to the database, but then the form is reset (wiping out my entries) so when the form goes to SharePoint, it no longer has the same information that went to the database. I can't unmark the calculation box because the defaults don't work at all.  How can I add a section to the second part of the formula that if the box is N, I can just select from the dropdown list (@Lookup). 

concat(substring(@PIC_Combined[. = @Lookup], 1, boolean((@CrewSame = "Y" and @RowCountT = 1) or (@Leg_No = 1 and @RowCountT = 1)) * string-length(@PIC_Combined[. = @Lookup])),

substring(@SIC_Combined[. = @Lookup], 1, boolean((@CrewSame = "Y" and @RowCountT = 2) or (@Leg_No = 1 and @RowCountT = 2)) * string-length(@SIC_Combined[. = @Lookup])))

Thanks

Sboyd

How are you submitting to the database and why does it reset after you submit?  typically on a submit the values would not be changed.

Is this formula a default?  You may have better luck using a rule on the drop down field to set the values.

Where do you pick the second crew values?  A screen shot might help or if you want to upload the XSN for us to see. You can attach under the options tab when posting.

 I will pull the form up from SharePoint, enter the data, submit to the database and then it submits to the document library.  The form is refreshing the values, therefore, overwriting my values before it goes to SharePoint.  The field is a dropdown selection with the formula as the default value so it will prepopulate the names with the main crew.  I tried using rules but they weren't working.  I also have managed to do this without code but because of that, I've had resubmit issues when I've used rules.  I got the form working very well with all the bells and whistles with it submitting to the database, but it was a whole different issue when the form was pulled back up in SharePoint to make any edits and resubmit back to the database. The crew values are coming from a secondary data source.

I can't attach the XSN file because it has company passwords in the form and I tried to attach some screen shots but was having difficulty.    Here are my submit rules: 

Sets a global field value to Y

Submits to the Main Database

Sets an Edited By field with Username()

Sets another field to say "Edit Mode"

Submits to SharePoint.