Question about Performing Queries on Subtables

Support
1

I have a sheet that has a subtable of price quotations from potential suppliers for a given inventory item of ours. Each price quotation in the subtable has an expiry date. My subtable has a default subtable filter to only show those quotations that are “Active” and not “Expired”.

On this same sheet I want to place several summary fields showing various statistics from the subtable (including the lowest price, highest price, total number of quotes, etc). For this I tried using MIN(value), MAX(value), COUNT(), and VLOOKUP(). Unfortunately, these query the entire, unfiltered subtable, and not just the filtered results I have showing on the sheet.

Is there a way to just show results from the filtered results? Or some sort of workaround? Maybe a special function that conditionally evaluates records in the subtable? ie. only records with a status field set to “Active.” I already have a status field in each record which can be set to either “Active” or “Expired.”

2

Hi,

It’s not supported to apply formula just to filter subtable, we would suggest the workaround below.

  1. Creating new sheet from subtable, the records in subtable would shown as an independent entry on new sheet.

  2. Create descriptions fields on the listing page of new sheet and using BBcode to apply formula to those description fields.

  3. You could filter on the new sheet, it might calculate from the filter results.

If there’s any field on the parent sheet you would like to show on the new sheet, please refer to this article:
https://www.ragic.com/intl/en/doc/56/Link-fields-from-parent-sheets

3

Is there some way of “locking” older records (on a conditional rule) that makes them no longer editable and/or removes them from subtable calculations. That would be ideal haha. #featurerequest

Edit: It seems there is already a way to lock records here: https://www.ragic.com/intl/en/doc-kb/273 and with an easier solution in development as well. Are these locked records exactly what I think they are? Locked so that they can no longer be edited.

4

Hi,

Yes, the solution here ( https://www.ragic.com/intl/en/doc-kb/273 ) is to lock entry automatically when criteria match after entry edit manually. However it won’t remove the records from calculations.

Another way is mass edit - lock entry, you could filter the entries that meet the requirement and lock them up, which could refer to this article.
https://www.ragic.com/intl/en/doc-user/5/Mass-Editing#2.4

5

Is there a way to automatically lock records (when criteria match) through daily checking instead of only checking after a manual entry edit?

6

Hi,

Currently there’s no way to lock records automatically, except for approval process. We do have some development plan related to this, set fields to be “read only” when it meet conditional formatting.

We’ve add you to the notify list, we’ll keep you posted in this thread.