Record formulas

Panorama X
1

Can I create record formulas in a specified field in Panorama X? That is, each cell in the field can have a different formula. Panorama like most database applications can create field formulas, but not record formulas in a field.

The reason I ask this question is that I want to build a multi-level Bill of Materials database with hundreds of thousands of records. Each level has a lead time and cost roll-up total, and it displays the unit costs for items within each level. The record formulas create the unit costs of the parent items in a level based on the unit costs and quantities of the respective child items which are in a lower level. Microsoft Project could do this by indenting the levels to perform the time (e.g., days) and cost roll-ups, but it cannot handle tens of thousands of raw material items. Can Panorama X indent records into say 9 hierarchical levels?

For example, can Panorama X have a multi-level BOM database with time and cost roll-ups for each level to build a Boeing Dreamliner airplane?

I have a BOM model in Visual DSS (Australian modelling software) but it can only handle 32,000 variables. I can send you a run-time version of this model which has natural (English) language formulas to perform the cost roll-ups.

2

I’m trying to visualize your data scheme and that action alone triggers this comment - It is not unusual for someone coming from one app to try and more or less exactly match the physical scheme in another. Two examples: Coming from a row and column Excel spreadsheet, there’s an attempt to model that same layout in Panorama. Or, people new to hammocks try to replicate their “bed” sleeping position even though the pressure points are different.

Leaving the second example aside, what can happen in the Excel exercise is the user makes a record for each category, cost, or income, and grows the database by adding fields for new dates. Don’t do that. Turn the spreadsheet on its side, make a field for each category, and add records for the dates.

Using various form objects/designs you can make it “look” like a pro-forma report but the data is stored and accessed better with fields for categories and new records for new dates.

I’m not saying you should redo the work. Just look at it and make sure you are open to alternate setups. For example, maybe it would work better if there were multiple databases for various components and their data is pulled into one database for the final result.

3

Can you? Yes. Should you? Probably not, at least not as you have stated it.

The formulavalue( function allows a formula to be constructed dynamically on the fly.

So, you could use a separate field to store an individual unique formula for every single record in the database. Suppose the field was called RecordFormula, then you could set the field formula to this:

formulavalue(RecordFormula)

So, you could put a different formula in every single record and Panorama will calculate using this independent formula.

However, this sounds like a recipe for chaos. Where are all of these formulas going to come from? Who is going to write these formulas? How will you ensure that the formulas are correct and error free?

I think it is highly unlikely that you will really have hundreds of thousands of different formulas that need to be calculated. Chances are, there are just a small number of different formulas needed, based on the other fields in the database. I think you would be much better off devising a single formula that can perform whatever calculation you need, even if that is a fairly complicated formula.

This sounds like a application where it would be much better to factor the data into separate databases with relational links. I think you would wind up with one much simpler formula, and data that is much easier to visualize and maintain.

4

Thanks Jim and Paul for your help.

To clarify things I was intending to write hundreds of thousands of formulas in Panorama.

The number of formulas is just a few, and the purpose is to summarize lead times and unit costs of component items to roll-up into parent items in an MRP (Materials Requirement Planning) database to update a BOM (Bill of Materials database).

If someone has sample MRP and BOM database Panorama files to help me, it would be most appreciated.

5

Frank_Wade, I am unfamiliar with that application, but the thing about databases is that they are the “bones”, the “structure” - a body upon which you put on clothes (less gruesome image than “flesh”).

In another life, when assisting callers with their database designs, I could see the structure behind what they were doing and pull a database off the (mental) shelf. The User could only see their specific implementation. But a database to keep track of payments to a dating site might be the same as one keeping track of church donations, and perhaps the same as totaling times in a multi-event track meet. Each person had a “unique” situation, but the underlying database structure was the same. An individual was identified, amounts (time, dollars) were accumulated - with a date component - and summaries were generated.

When I read that you plan on adding formulas in the tens of thousands, it triggers a large orange warning sign in my head. You can apply ONE formula to tens of thousands of records at once.

I’m not familiar with the language of your project but it sounds like you would be using the PanX GROUP ability. For example, If I had three movies and I wanted to track income from the snack bar for each, I could input the revenue with the “unit” being a dollar amount for a specific snack item during a specific movie on a specific date. So, date, movie title, snack item, and price - maybe quantity, price each, and total price.

Using PanX’s Group- ability, I could “roll up” those figures and see the total revenue for each snack item for each movie, with further breakdown by date. I could wrangle it any way I wanted - say just the totals for each snack item. or totals on the weekend compared to mid week, etc.

Can you see the bones? Can you see that be it a movie with a snack bar or an Aeroplane with “Parent Items” that have individual daily cost items - child items - you can group the child items and parent items and with one of two actions, see the child totals and the parent totals of the children.

PanX allows for 7 summary levels. You mentioned “…9 hierarchical levels?” but as mentioned before, you may not need to keep all the data in one database. You could, for example, have a couple of databases for various items, summarize those, and then bring those summaries into more of a “master result” database.

Here’s the image in my head. I am tasked with giving 5 juggling balls x amount of air time. I can try to juggle 5 at once (which I don’t have the skill to do), or I can juggle 3 for x amount of time then two for x amount of time. By the way, “time” in PanX can be fractions of a second.

Finally (for now) understand the language … gap. When you say your BOM model can handle only 32,000 variables, it might be that what you are thinking of as “variables” might not be what I am thinking of as variables.

I’m starting to get a feel for this now - if I’m on track. Let’s say you want to build [something] and that something has various parts and labor cost could also be a “part”. And at the end, or any time during the project, you want to see the total quantities/costs, for those parts.

Yes, you can absolutely do that and you don’t need to write tens of thousands of formulas.

6

I don’t think you need to write the formulas for each. Rather some careful planning of your data structure - as Jim noted above - will allow a lot of the data to sort itself for you. Since you’re familiar, you already get the idea of the MRP — millions of details are gathered but the report itself has summaries. Think of those as nodes.

So a room in a building to be designed has parts, labor, design costs, etc. Each of the those atomic items are classified (first node) and then grouped to an object (second node) and then all those items grouped to the room (third node) and the room grouped with the rest of the floor (fourth node) and on and on until you have a master summary for the building. Same idea. This is done with relational databases easily and designing the data structure. Although I am new to panorama, what I’ve done with it so far convinces me that it can handle a project like yours very capably.

Dig in a little with it and play with relationships and one-to-many lists. The lists (like the node I mentioned above) can aggregate and calculate and other lists can aggregate and calculate and so on, each summary at a node being built by gathering the atoms related to it.

1 Like
7

Mine too, Paul! :scream:

You could do that, but I think that is something that you would almost certainly regret later.

:+1:

8

kinda sounds like multiple filmed and generated elements “rolled up” to comprise a single run of frames (a shot) in a film. Every shot can have multiple versions, and multiple iterations on that version, and each needs to have notes and costs associated with it. Elements can be shared by numerous shots (like a special fastener could be used in multiple places on the plane) And you would have to be able to sort and group so you could be sure you had made enough of these fasteners before you could finish the doodad that they are used in.

I know how to do this :stuck_out_tongue: I’m not sure i know how to explain it succinctly, but it sounds like a fun project!

9

Frank_Wade, perhaps you can give us a small, real-life example of the “roll-up” you need. I’m beginning to think your thousands of variables are just objects. That’s just one variable - an Item. The content of Item can have thousands of different entries, but its one variable.

For example, Let’s say I have a database with 1000 records. I have a field called Item and a field called NumberOfItems. As I amass the data - those thousand records - each record having data designating the Item - a bolt, a nut, a flange, etc., and a quantity. Thousands of different items are possible.

At some point, I want to “roll up” that data so I have totals for all the unique items. That takes a few clicks in Panorama and NO formulas.

In the Help area, look at GroupUp, Total, and Summery Recod actions

GroupUp (I think that is somewhere in Analyze on the item field - that will cluster the items together and create a summary record for each unique item. Then you’d move to the NumberOfItems field and Total (I think that is now somewhere in Morph). Then you collapse the records and you’ll see just the records showing the over-all totals for each different item.

Without a small, simple, example of what you need done, that’s the best I can do.

It’s best to practice/learn with a small simple case. Make a new database with a text field named Fruit and a numeric field named Qty. Add some records with several different fruits - like three apple records, 2 orange records, five banana records. Put a number in the Qty field for each record. Now to roll up.

You’d GroupUp - in the datasheet view, by selecting the Sum Tool (three over from the record count display at the top/middle of the datasheet). Select Summerize and Analyze and select your Fruit Field to group up. Select Qty in the box to the right of the Total (you won’t see it until you click in the box)… Then push the Analyze button at the top right and you’ll see the datasheet with the Fruit rolled up - just showing a record for each fruit name, the totals per fruit, and a grand total of all the Fruit names.

So that seems like a RollUp to me. I know I’ve seen dried fruit rollups in health food stores. But they cost money. Panorama - a database - provides that feature at no extra charge.