Hello everyone.
I have a huge problem. Although I have now seen some videos from the help section on relational databases, I am still struggling to set up the databases with the corresponding forms correctly.
Are there any databases here that you can download that have some relations set up? So that you can have a look at the interaction? Especially with forms. What I don’t understand at all is the thing with the line items. If I now - as an example - set up 6 line items, but then want to write an invoice with 7 items… what then? Do I then need 7 line items? Can I set this up differently so that I remain flexible with the number of my invoice items? How is this question solved in the form? A form that grows with the number of items entered? How do my entries in the form get into the linked database?
Sorry, but I don’t understand. Is there perhaps a simple example (in the form of a file / files)? I would very much appreciate it.
In answering just a part of your post about line items… Some time ago I found that using multiple records for an invoice gave me much more flexibility in the number of items in the invoice. It didn’t matter if there was 1 or 100 items.
Each record had a field for the invoice number making it easy to keep them organized. I could readily select all records for invoice number 123, run totals, and so on.
A simple procedure can enter a new record for the “active” invoice. Once finalized, a field could be filled with an indicator that the invoice shouldn’t be modified by adding, deleting or changing records.
Just one way to skin that cat, but it has worked well for me.
Ok. So let’s assume, I have this database “invoice_items” with the items for invoice “123”. But now I have another DB “invoices”, where I want to have an overview of the invoices. Like invoice #, customer, customer address, grand total etc. How do I get the total of invoice “123” into this DB? Must be some kind of sum. But how do I get this sum?
Next thing: How do I set up such a simple procedure for creating a new invoice number? And where?
Are you familiar with Summary records? If you Group by invoice numbers, you can run a Total on the amounts field, Propagate on the client name, address… Appropriate data from any column can be placed into the Summary record.
Collapse the file to just the summaries and you may not need a separate file for your overview.
AddRecord
«Invoice #» = 123
Assuming Invoice # is a numeric field.
You can also use the aggregate( function, which doesn’t require using summary records or the Group statement.
Yes, exactly. You decide the maximum number of line items needed in advance.
As Jim Cook mentioned, there are a couple of techniques that can allow an unlimited number of line items. One would be to use a separate database, as Jim mentioned. The other technique would be to use a text array. However, both of these techniques are much more complicated to implement than line items.
Ok. So I tried the aggregate Formula and stumbled upon something:
I wanted to aggregate the positions of invoice “123”, which is 100.
With the formula:
“aggregate({total},“sum”,{Invoice = “123”})”, the result is correct, with the formula
“aggregate({total},“sum”,{Invoice = fieldvalue(Invoice)})” the result is 1100, although “fieldvalue(Invoice)” equals “123”.
So my first question is: Am I doing anything wrong here?
And my second question: When I use this formula in the formula section of a field, nothing at all will be happening. So where else - if not in the formula workshop - can I use such a formula?
Instead of total, you want «item total», based on your field names
I guess, that’s ok.
And also, it’s working with the formula, if I enter the condition like Invoice = “123”.
cremoer, though it may work (sometimes) I’d stay away from giving fields or variables the exact same name as Panorama verbs and function names.
For example, I would NOT name a field or variable Total, Sum, Date, etc. You can include those a part of the name, i.e. invoicetotal, and with the advantage of more clarity - answering the a question you (or someone else) might ask yourself a few months from now, “What total is it?”
I am not saying you can’t use those special names. I’m just saying, from experience, that doing so may cause confusion later on.
You are absolutely right, and normally I don’t do this. Because I am German and choose rather German words in my workflow.
But even if I choose a different name here - like “positionssumme”, this “fieldvalue(Invoice)”, which equals “145” in this case, is obviously not evaluated properly. Because the sum of Invoice “145” is 1000, and not 1100.
Actually, you are. It’s a bit subtle, so I don’t blame you.
The fieldvalue( function gets the value from the current record. But what is the current record? Usually, it is the record that is highlighted in the data sheet. But for operations that scan the database (like the aggregate( function does), the current record is the current record being scanned. So your formula
Invoice = fieldvalue(Invoice)
is essentially the same as
Invoice = Invoice
Since this will be true for all records, you get the sum of all records, which is 1100. Voila.
The solution to this is to create a variable that holds the invoice number, and then use that variable in the query formula.
let targetInvoice = Invoice
let invoiceSum = aggregate({total},"sum",{Invoice = targetInvoice})
This can also be done entirely in a formula:
ignore(0,cache(Invoice,"targetInvoice"))+aggregate({total},"sum",{Invoice = targetInvoice})
The cache( function creates a temporary variable that contains the invoice number. We don’t want the invoice number included in the sum, so we enclose it in the ignore( function, which in this case will always return zero. So the result of the first part of the formula is simply zero, but with the side effect of creating a temporary variable. Then the second part of the formula calculates the actual sum, which is added to zero so the correct value is returned. You should be able to try this in the Formula Workshop.
Guess what - my revised formula should sort of work in the Formula section of the A field. When you modify the Explaining why this works and the other doesn’t is a bit tricky.
Whenever a field is modified, Panorama scans all of the other fields in the database, looking at the formula for each field to see if it needs to be updated. Now you might think "sure, the aggregate function references the total and Invoice fields, so that formula should be recalculated if either of those fields changes. However, the formula scanner isn’t that smart. The fields in the aggregate( function are inside text constants - they are included as text, not as field names. So Panorama doesn’t recognize that it needs to recalculate when these field names. This will be true basically for any function where the formula is used to scan the database, for example arraybuild(, aggregate(, superlookup(, etc.
However, in my formula, the Invoice field in the cache( function is specified as a field, so it will be recognized. You would probably also want to recalculate for the price and total fields, so those could be added to the ignore function like this:
ignore(0,cache(Invoice,"targetInvoice"),price,total)+aggregate(...
However, I don’t think it makes sense to assign this formula to the A field, in fact I think your A field is not really a great design choice, since that will give you multiple cells with the invoice total. You really want that field in a separate invoice database. (I’m guessing you were just setting this up as a temporary test.)
I agree with Paul, but it’s actually fine with Panorama if you do this. Panorama can tell from the context whether a token is a statement, function or field name. But it might confuse you. For example, in your procedure you could have a procedure with this code:
field total total
This tells Panorama to
- Go to the total field
- Calculate the total
This will work fine - but maybe it’s not so clear just looking at the code!
Wow! Thanks a lot for this precise and elaborate answer.
I think, this clarifies a lot.
And you are absolutely right, I did put the formula into the “A” field just to get a feeling where to use (or not use) such formulas.
It’s not quite easy, but it sounds logical somehow.
I now have to see if I can get the hang of it 
In produce wholesale or any other wholesaling business for that matter, customers can order from one to several hundred products. Using multi record invoices makes this possible. Unfortunately, the View as List form that made this easy in Pan 6 is not fully supported in Pan X and It has been a frustrating year long project to come up with a usable work around.
A text list is probably the closest you can come. It can be set into a form with an adjacent set of cells to allow editing the active record.