How to get matching pair of data from 2 multi-field columns?

Question

In my Lotus application, there's a table with 2 columns: Certificate and Certificate No.

There are 10 rows in total and each row have a field for both columns. So 10 Certificate fields and 10 Certificate No. fields.

Moving to View, both Certificate and Certificate No. have a column each, collecting all 10 fields into the columns. Both uses column value formula:

When displaying, instead of both columns pairing with their correct row fields, i.e cert_1 with certNo_1, Certificate ended up pairing with all Certificate No.:

Do I need to change my column value formula to fix this? What would be the solution?

Answer 1

OPTION 1

Combine the two columns together in one column.

Set the formula to:

cert + " " + certNo : cert_1 + " " + certNo_1 : cert_2 + " " + certNo_2 : 
cert_3 + " " + certNo_3 : cert_4 + " " + certNo_4 : cert_5 + " " + certNo_5 
: cert_6 + " " + certNo_6 : cert_7 + " " + certNo_7 : cert_8 + " " + 
certNo_8 : cert_9 + " " + certNo_9 

The column will show the cert and certNo paired up correctly.

You may need to do some conditional checking to see if the cert or certNo are populated e.g.

@If(cert != ""; cert + " " + certNo; "")

If the above is not an option, do the following:

OPTION 2

Set the sorting on the Cert column to Ascending or Descending, and tick "Show multiple values as separate entries"

Then on the Cert No and Date columns, set the Sort to "None" and tick "Show multiple values as separate entries"