Hi everyone, I was working on a system for construction/electrical estimating and I ran into a problem that I think can be solved with scripting, but unfortunately I know very little JavaScript. I was hoping to get some guidance to see if it’s even possible/worth trying to figure out myself.
TLDR: I’m trying to make an automation the makes new price entries for a category of materials that are an average of all of the most recent prices for the items in the category any time each of the items is updated. The categories are made self-referencing multiple materials to a single new material.
I would be happy to share the template with anyone if they want to mess with it. I’m pretty happy with the function of everything else. Also, sorry in advance for the novel.
Estimate contains estimates for all of my projects with request dates and other metadata (not linked in my test example). Estimate Material contains a list of materials for each Estimate with a specific quantity. Estimate Material gets all of the info about each item from Material which is a DB of most of my commonly sold materials. In order to track changes in material cost over time, updated prices are added to Material Pricing periodically and then automatically linked to Materials using text columns (probably SKU numbers, I haven’t totally decided).
The point of all of this is so that I can get the most up-to-date pricing for an estimate when it’s first created, but I can go back and look at old estimates and have correct historical pricing data. At the moment this all works great, but there is a catch.
The Problem:
I am trying to move away from specifying everything as an exact item for my estimates and instead estimate with categories of products for certain things. This means that the estimate would have a combination of material categories and specific items (think electrical boxes in general and a specific type of wire). I was able to create these categories in the Material by using a self-reference.
The problem is that I’m using a formula in Estimate Material to find the most up-to-date material price before the creation date of the Estimate and it’s not possible using formulas to filter related entities by unique value. I can average all of the historical pricing for all of the items in the category for all time, but I can’t just average the most up-to-date prices for each unique item. My idea for a workaround was to trigger an automation that added a new Material Pricing entity whenever another entity was added that was part of a category. The automation would create a new entity with JS that would average the most recent cost off each of the related items in the category.
I’m trying to write an automation that triggers whenever an entity (Material Pricing) is added for a Material that is referenced by another Material (materials are self-referenced to form categories). The automation would need to add another Material Pricing entity for each of the Material entities referenced by the trigger. The new entities for the categories would need to have an average cost of the most up-to-date price of each of the items in the category.
Also, thank you to anyone who manages to ready the whole thing. It made my brain hurt trying to explain it and I figure it might make your brain hurt trying to read it.
Hi Tommy.
It’s an interesting problem indeed. Can you summarise what would you javascription function need to do in plain english?
Example(not relevant for your case I just made it up):
Find estimate material for current material
Get prices for all estimates and average them if date is less than creation_date
Trigger an automation when a new price is added for an item that is part of a category. I’ve managed to figure this out by adding a checkbox formula to the price DB and using the automation filters.
Identify which categories the item from the trigger was a part of.
For each category get the most up-to-date price of each item in the category and get the mathematical average.
Make a new price entry for each of the categories and record the new average price and date.
It feels like it ought to be possible with lookups and formulas. Would you be willing to share your space with me and I’ll have a go at seeing what’s possible.
You could either invite me in, or share the space as a template and send me a link.
// Assumptions - feel free to change them
const CATEGORY_TYPE = 'Category';
const CATEGORY_FIELD = 'Categories';
const FIELDS_FOR_CATEGORY = ['AvgPrice'];
// retrieve categories for current entity
const entityWithExtraFields = await fibery.getEntityById(entity.type, entity.id, [CATEGORY_FIELD]);
// find ids of categories this item belongs to
const categoriesIds = entityWithExtraFields.map(x => x.Id)
// load categories from fibery
const categories = await getEntitiesByIds(CATEGORY_TYPE, categoriesIds, FIELDS_FOR_CATEGORY);
Pretty sure you can use formula to do part of that that. Let’s say we make a field AvgPrice:
// assuming that Items are linked to category and have price field.
Items.Avg(Price)
Adding to step 2:
const PRICE_ENTRY_TYPE = 'PriceEntry';
// for each category we add a price entry using add collection item
const categoriesAddition = categories.map(async cat => {
// create new price entry
const priceEntry = await fibery.createEntity(PRICE_ENTRY_TYPE, { price: cat.AvgPrice })
// link price entry to category
return fibery.addCollectionItem(CATEGORY_TYPE, cat.id, 'PriceEntries', priceEntry.Id);
});
// Wait for all addition to finish
await Promise.all(categoriesAddition);
Hope that sets you on the right track.
Alright, I don’t have it quite solved, but I think I made my scripting requirements much easier. I thought I would take @Chr1sG’s advice and try to see if I could do more with lookups. It turns out I can! I managed to make a formula in the Material DB that is the most up-to-date average price in categories. This simplifies the scripting requirements a ton. Here is a link to the space if you want to check it out: Estimate System Test — Fibery
I think all I need is as follows:
Trigger a rule when the average price for a category in the Material DB changes. Easy
Create a new price entry in the Material Pricing DB for the category with the updated price. All the entry should need is the new average price and the material “code” text because the Material and Material Pricing DBs auto link by matching the “code” text in each DB.
Sorry, I know this is pretty simple JS and I am trying to learn more. Thank you guys so much for your help, this community is awesome!
I just figured it out! I sifted through old API & Programming threads and found this as a solution. The modified script looks like this:
const fibery = context.getService("fibery");
for (const entity of args.currentEntities) {
await fibery.createEntity("Estimate System Test/Material Pricing", {
"Code": entity["Code"],
"Cost": entity["Current Average"],
});
}
Thank you @Chr1sG and @tpaktop for your help! I will leave to template link to the space if anyone wants to mess with it, I think it turned out pretty cool! Estimate System Test — Fibery
