Tag Archives: Dwindling Value List

User-Friendly Excel Exports, part 7

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

This is a follow up to last week’s part 6, with three refinements.

Continue reading

User-Friendly Excel Exports, part 6

16 Sep 2017: this file (Excel Exports, v7d) fixes a bug where empty fields would cause values to appear in the wrong columns.

29 Mar 2017: this file (Excel Exports, v6d) fixes a formatting bug when currency values < 1000 are not preceded by a “$”. Explanation forthcoming in next week’s “part 7” article.

Recently I was asked to implement user-friendly Excel exports on a WAN-based solution, utilizing some of the techniques explored earlier in this series (part 1, part 1.1, part 2, part 3, part 4 and part 5 — all from five years ago). There was just one problem: while performance was great locally, and okay on a LAN, it was decidedly less-than-satisfactory across a WAN.

Well today we’re going to look at three demos showcasing a brilliant, outside-the-box technique I picked up from Beverly Voth, which is not only significantly faster on a WAN (4x or 5x faster), but also remedies some other shortcomings associated with my earlier approaches.

Demo files:

Continue reading

Dwindling Value Lists, part 1

A few months ago I mentioned “dwindling value lists” in passing, and said I would do a proper article on them at some point in the future. Well, then FM 12 was released and I went on an ExecuteSQL binge, so I’m just now getting around to honoring my promise.

Dwindling Value Lists (DVLs) are value lists that shrink up, by removing individual list items as they are selected. They can come in very handy when you need to schedule resources and want to prevent double booking. This example comes from one of today’s demo files, Dwindling Value Lists, and shows how a DVL might be used to schedule employees for a work shift.

Essentially, a DVL is a filtered value list that updates in real time (or something very close to real time), and this is done by building a multiline key of selected values, and then filtering the VL to only show remaining eligible values. Continue reading

User-Friendly Excel Exports, part 4

27 Mar 2017: see User-Friendly Excel Exports, part 6 for an updated approach.

Colleagues and family members are starting to express concern at my obsessive refusal to bring this series to a close. But we’re getting close. Really. In fact, today, we’re going to look at two cool tricks (and one work around) which are utilized in this article’s demo file, excel exports, part 4, but that could prove useful in a variety of other situations. They are:

  1. Dwindling Value Lists
  2. Using a variable rather than a hard-coded file reference as an import source
  3. Adapting the translation table technique to work with Anchor/Buoy (or any other multiple-table-occurrence-group design philosophy)

Dwindling Value Lists

I’m just going to touch briefly on these today because they are fully deserving of their own topic, but the technique is ideally suited to the task at hand. We want the user to be able to flexibly pick “fields” (actually pseudo-fields, as per earlier articles in this series) to be exported, assigning them to columns A, B, C, etc.

Once a field has been assigned to a particular column, we really don’t need or want to see it in the list of choices any more, do we? Ideally the value list should “dwindle” (shrink) as items are chosen, to make things clearer for the user. (You will note that in the above screen shot, the items in columns A through E, having already been assigned, do not appear in the pop-up menu.)

Continue reading