How can I get the first or last record on a subtable? Can I use the Aggregate operations on a subtable?
It depends on your use case and the following two questions, assuming that you intend to sort the subtable from a Logic Block and not in an Application:
- Is your subtable of type Main or type Sub?
- Are you reading data or making modifications?
Subtables of type sub do not support fetches and aggregations; they should only be obtained via the Loop Subtable logic action. Loop Subtable does not support sorts.
Subtables of type main can be retrieved via the Loop Subtable action or a Fetch Records or Aggregate action.
If you need to modify data in the subtable main, best practice is to use the Loop Subtable action to make those modifications, which means that you cannot sort the records and must loop until you find the record(s) you intend to modify.
If you are only reading data from the subtable main, you can utilize a Fetch Records or Aggregate logic action to find that data and sort it accordingly. Those fetches should abide by the standard best practices of limiting to only the records you need and fetching over an index. Again, this fetch should not modify table data.
3 Likes
When should I use type Sub? Are there any advantages of using type sub?
A subtable Sub has a few quirks and is less commonly used, but it is generally a simpler construct and preferable if you never need to get data from the subtable outside of the context of the parent table.
The Nextworld Help documentation article “Subtables” shares some details:
Generally, Sub means that the data is tightly coupled with the parent record: there is no means to directly fetch data from it, there are no defined indexes, and security does not apply. One of the perks is simplicity of the data model and their usage in work tables, as needed.
2 Likes
How do you sort a subtable if it is within an app (and not the logic block use case)?
1 Like
Sorting subtables is not supported within an application. Filtering is possible by selecting the Filter toggle in the dropdown menu located in the top-left corner of the subtable.
The order of subtable records within an app remains the order in which those records were inserted.
As a citizen developer on the platform you should never use this type for creating regular subtables. Its only valid use case that I’m aware of is associated with creating Data Item Groups.
2 Likes
If you absolutely must reorder the records in a subtable automatically like a sort does, what I have done in the past via a logic block (which can be called from the UI by some sort of action) is:
- create a temp table over the same underlying subtable
- loop the subtable (using the designated LB subtable action for this) and copy the records to a temp table
- clear the subtable (using the designated LB subtable action for this)
- fetch the temp table and apply the desired sort on this fetch
- reinsert the records back to the subtable (using the designated LB subtable action for this)
2 Likes
I disagree with this in practice - subtables of type Sub are absolutely a valid construct for normal development. While there are a good number that act as Data Item Group containers, they are more common on the platform than would be expected, and a lot of the object builder apps use many small subtables for object configuration (think Table Definitions or Permission Definitions).
Sub subtables also have the benefit of being stored on the parent record directly, not in their own table, so there’s never additional database trips involved.
For a basic app that expects a relatively small number of subtable records, these can act as a good storage option or a temporary ‘preview’ of example data within configuration records.
1 Like
@ross yes Subtable Subs have their appropriate usages, I believe it is considered best practice to use only Subtable Mains like @ian.p said if you are building an application for a business use case like Journal Entries, not a development use case like Table Definitions. So I would add a caveat to your response here based on who is asking this question and it’s context.
2 Likes
This is a good call. I got confirmation that the best practice is to use Subtable Main and not Subtable Sub. This is a context-dependent decision and there are exceptions (like when the subtable is being used as a work table and will never store data), but it is a general rule.