My post yesterday was a little wrong. Here is a better SQL to help with the sequence of the fields in the layout:

SELECT * FROM (

  SELECT A.FLDFIELDNAME, A.FLDSTART,

        (SELECT COUNT(*) FROM PSFLDFIELDDEFN

         WHERE FLDDEFNNAME = A.FLDDEFNNAME

         AND FLDSTART < A.FLDSTART) + 1 CORRECT_SEQ,

         A.FLDSEQNO ACTUAL_SEQ

  FROM PSFLDFIELDDEFN A

  WHERE A.FLDDEFNNAME = '<file Layout Name>'

)  B

WHERE B.CORRECT_SEQ <> B.ACTUAL_SEQ

ORDER BY B.FLDSTART

I had trouble rearranging the fields though. You can drag and drop the fields, but when you reopen the file layout, you find that it didn’t actually adjust the field sequence (FLDSEQNO). So, the fields are back in the order they were in. This is true for tools 8.46; I didn’t test 8.49.The only way I could find to rearrange the fields was to delete the field from the layout. Then, click on the field that you wanted it after and re-insert it.

Here are a couple of SQL statements that might help you maintain file layouts.

This SQL shows gaps in a flat file. It assumes that the next field should start at the previous field’s start + the previous field’s length. Make sure to substitute the file layout name.

SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSTART + A.FLDLENGTH SHOULD_START_POS,

       B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART ACTUAL_START_POS

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B

WHERE A.FLDDEFNNAME = '<file Layout Name>'

AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

                  WHERE FLDDEFNNAME = A.FLDDEFNNAME

                  AND FLDSTART > A.FLDSTART)

AND A.FLDSTART + A.FLDLENGTH <> B.FLDSTART

ORDER BY FLDSTART;

This SQL shows if the fields are out of order. If the order in the file layout designer is different from the order based on the field start position, the layout will be a little confusing and hard to maintain. This should help you correct the order. To re-order the fields in App Designer, drag the field on top of the field that you want it to come after.

SELECT A.FLDFIELDNAME, A.FLDSTART, A.FLDLENGTH, A.FLDSEQNO, A.FLDSEQNO + 1 SEQ_SHOULD_BE,

       B.FLDFIELDNAME NEXT_FIELD, B.FLDSTART NEXT_START_POS, B.FLDSEQNO ACTUAL_SEQ,

       C.FLDFIELDNAME ACTUAL_NEXT_FIELD

FROM PSFLDFIELDDEFN A, PSFLDFIELDDEFN B, PSFLDFIELDDEFN C

WHERE A.FLDDEFNNAME = '<file Layout Name>'
AND A.FLDDEFNNAME = B.FLDDEFNNAME

AND A.FLDDEFNNAME = C.FLDDEFNNAME

AND B.FLDSTART = (SELECT MIN(FLDSTART) FROM PSFLDFIELDDEFN

                  WHERE FLDDEFNNAME = A.FLDDEFNNAME

                  AND FLDSTART > A.FLDSTART)

AND C.FLDSEQNO = A.FLDSEQNO + 1

AND A.FLDSEQNO + 1 <> B.FLDSEQNO

ORDER BY FLDSTART;

Just a quick update:  I added the File Layout Definition tables to the PeopleTools Tables page.  Hope it helps.

I had used one of the tables on this post, but I never added the table to the reference page.