How to filter out empty item fields out from a looked up data source

Question

Situation

Look up SharePoint list has several columns. Each column is to be used as a look up data source for a drop down or multiple-selection list box (the exact control type is irrelevant) in an Infopath 2013 form.

Some columns do not have the same number of populated fields. For instance, one column has 50 items, and another column has 10 items.

Issue

For the Infopath control that looks up in the list column that has 10 items, even with ticking in the control properties

Show only entries with unique display names

the drop down list box displays the 10 items as predicted + one empty item, as the 40 (50-10) empty items are understood by the look up as a 'display name'. This empty item can be selected as a valid answer when the form is being filled in.

Question

What's the best way to prevent this empty field to display?

Is it only a matter of creating different look up lists (and having various data in the same look up as the data source is bad practice), and there should be a separate SharePoint custom list for each data source needed for various Infopath controls?

Answer 1

Your own thoughts at the end would likely be the best answer. Lookup fields are SP's method of creating relational databases. Having a "master" lookup list is kind of counter-intuitive to how that should function, because then you're essentially associating a # of unrelated fields with any items you link to one of these lookups. While you can "mask" this in various ways to make it still work the way you want, I would consider having separate lists per lookup to be best practice.

That said, sometimes it does make sense for one list to be servicing multiple lookup fields. However, that would be lookups from different lists. If List A has a lookup field pointing to column 1 in List B, you shouldn't be creating any additional lookups to List B w/in List A, but List C might have a lookup field to column 2 in List B.