Creating a filtered collection field on a type

#1

I have 2 types:

entity type FinishedGood ... {
  rawMaterials: [RawToFinished](finishedGood)
  globalSalesCode: string
  sales: decimal
...
}

entity type GlobalSalesCodeAggregate ... {
  globalSalesCode: GlobalSalesCode
  finishedGoods: [FinishedGood](globalSalesCode)
  sales: decimal stored calc 'sum(finishedGoods.sales)'
  fgCount: decimal stored calc 'count(finishedGoods)'
 ...
}

I want the collection GlobalSalesCodeAggregate.finishedGoods to only have finished goods that have count(rawMaterials) > 0.

Any ideas on how to create this entity?

#2

In general, you can use .(FILTER). in your expression to do a filter in a calc field.

I would probably do something like this (note that i use collection fields):

entity type FinishedGood {
  /**
   *  General advice here, don't use strings, use references to objects.
   *  Here we would use the id of the global sales code object we're trying to reference
   */
  salesCode: GlobalSalesCode
  rawMaterials: [RawToFinished]
  ...
}

entity type GlobalSalesCode {
  /**
   *  Note that this field is not stored, because its a fkey field
   */
  finishedGoods: [FinishedGood] (salesCode)
  
  goodsWithMaterials: [FinishedGood] stored calc 'finishedGoods.(count(rawMaterials) > 0)'
}

I’m not 100% sure about that expression, you might have to try:

"finishedGoods.(count(rawMaterials) > 0).id" 

because maybe you can’t have the dot-query be the end of the expression…

I’m not sure why you have a GlobalSalesCode and GlobalSalesCodeAggregate as separate types, but thats separate

#3

@Susan, You can annotate the GlobalSalesCodeAggregate.finishedGoods as follows:

entity type GlobalSalesCodeAggregate ... {
  globalSalesCode: GlobalSalesCode

  @db(filter = "count(rawMaterials) > 0") // this filters the fkey array as you want.
  finishedGoods: [FinishedGood](globalSalesCode)

  sales: decimal stored calc 'sum(finishedGoods.sales)'
  fgCount: decimal stored calc 'count(finishedGoods)'
 ...
}

Of course you need to be mindful of performance, depending on how this field is accessed.

#4

@trothwein Would you prefer this method over @rileysiebel’s method? Also is that a permanent filter or is it overridden when fetching with a separate filter?

#5

@brett.thompson It really depends on the data and how the field is going to be used. The filter will be evaluated every time the field is accessed, so if it is slow it will hurt performance. Also, if you want to filter to see if a particular entry exists for the field, it will have to be a subquery on top of that. So, if either of those situations is true, the stored calc approach would be better.

As for whether it is permanent, yes it is. The filter will be applied in addition to any other dot query specs for the include. Filtering through the field “should” work and if it doesn’t, that would be a bug.

#6

I’m unable to get this to provision. The error messages are this:

  • {fileUrl:“meta://3m/rmr/src/aggregates/GlobalSalesCodeAggregate.c3typ”,lineNum:1,colNum:1,severity:“ERROR”,message:“Array field GlobalSalesCodeAggregate.fgsWithRms does not specify a schema name or valid chain of suffixes”,targetType:“GlobalSalesCodeAggregate”}
  • {fileUrl:“meta://3m/rmr/src/aggregates/GlobalSalesCodeAggregate.c3typ”,lineNum:1,colNum:1,severity:“ERROR”,message:“Unable to upsert collection for type GlobalSalesCodeAggregate: Array field GlobalSalesCodeAggregate.fgsWithRms does not specify a schema name or valid chain of suffixes”,targetType:“GlobalSalesCodeAggregate”,listenerType:“UpdateSchemaListener”}

Here’s the actual code of the last thing I tried:

entity type FinishedGood mixes MetricEvaluatable schema name “FINISHEDGOOD” {

legacyId : string

//sapId
sapId : string

//Item master
item: ItemMaster

// collection of Raw Materials for this finished good
rawMaterials: [RawMaterialToFinishedGood](finishedGood)

// sales for this finished good
rlng12moGrossSales : decimal

//description
description: string stored calc 'item.description'

//business id
businessId: string stored calc 'item.globalSalesCode.profitCenter.businessId'

//stored calc for division Id
divisionId: string stored calc 'item.globalSalesCode.profitCenter.divisionId'

//profit center Id
profitCenterId: string stored calc 'item.globalSalesCode.profitCenter.profitCenterId'

//product group 2
productGroup2: string stored calc 'item.globalSalesCode.productGroup2'

//gsc
globalSalesCode: string stored calc 'item.globalSalesCode.id'

}

entity type GlobalSalesCodeAggregate mixes MetricEvaluatable schema name “GSC_GROUP_AGG” {
name: string
globalSalesCode : GlobalSalesCode
finishedGoods: FinishedGood
fgsWithRms: [FinishedGood] stored calc ‘finishedGoods.(count(rawMaterials) > 0).globalSalesCode’
ttmSales: decimal stored calc ‘sum(fgsWithRms.rlng12moGrossSales)’
fgCount : decimal stored calc ‘count(fgsWithRms)’
}

#7

Your error message contains a hint:
type GlobalSalesCodeAggregate: Array field GlobalSalesCodeAggregate.fgsWithRms does not specify a schema name or valid chain of suffixes

change this field as so:

fgsWithRms: [FinishedGood] stored calc ‘finishedGoods.(count(rawMaterials) > 0).globalSalesCode’ schema suffix "FGSRMS"

Since this is a collection field, you have to give it a “schema suffix” to tell the db engine what table to create to store it.

#8

Btw, you should definitely not need this field:

globalSalesCode: string stored calc 'item.globalSalesCode.id'

Instead do:

globalSalesCode: GlobasSalesCode stored calc 'item.globalSalesCode'

This will still store the id as a string, but will allow your code to know more about that string, i.e. that its the ‘id’ of a GlobalSalesCode object. References are persisted as just an id string anyway, so the storage will be the same.

1 Like
#9

Yes a stored calc array needs a table to persist the data in. Add the following to the end of the field in question (it doesn’t seem to be in the snippet provided).

fgsWithRms : ... schema suffix 'GSC'

This will create a table with the name rootschemaname_GSC

where rootschema name is the schema name defined for the type.