Invoice system with relation tables

Where can I find a demo(panorama database) of a invoice system (Contact-quote-invoice) like this one I made in FM.
Or witch video do I need to view in the panorama x intensive training. I buy several but none taking about relations (one to many and many to many). My system have no limit of line and several table(external).

It is a db link to other tables: Invoice --> Invoice item and invoice–> contact quote transfert to invoice
Thank you for your help.

Relations in Panorama need to be built up according to your needs. You can relate files by lookups and by arrays. If you only need to view data from another file, you can put the lookup or array into form elements. If you want to copy data from another file, you can copy from fields in one file to fields in another. Everything can be done to suit your requirements. In order to have the freedom that you have in Panorama, you will have to do quite a bit of programming.

So I suggest that you study the available information on lookups, arrays, and form elements. Read the help and watch the videos.

It will still require your imagination to put them together into what you want, so at the same time, you should think about what it is that you want: What your data are, how you will input them, where you want to store the data, how they should relate to each other, how you want to use them, how you want them displayed. This is a lot of work that you have to do even before you start programming Panorama.

There are all kinds of ways to accomplish the task of building an invoicing system. It largely depends on your skills and ambitions.

Here is an example of one way that I’ve done it, although client needs have caused me to use other methods at other times. The example is using Panorama 6 but it’s applicable to Panorama X as well.

1 Like

Thank you. I design and program several system but I use other application Omnis,Filemaker, etc… I try to have information or video to learn the best way to do it with panorama x. I need to be able to edit a “portal” (a table in a table) in the invoice module .

One difference you will find in Panorama is you are responsible for setting up the relationships. Often I find people trying to “exactly” copy one system to another and they run into a wall. That’s because a third file - that they didn’t have before - is needed. Here’s an example. Say you have students and you have classes. You want to bring up all the classes a particular student is, or has, taken. You also want to bring up all the students who have taken a particular class. The usual filters based on date, currently taken or in the past, completed or not, etc. can be applied after the initial selection is made.

To collect all the classes for a student, or all the student for a class, you need a third file. It is a very simple file for all students - one record giving the student number, class number, class date, and particulars like completed or not and grade.

To get all the classes classes a student has taken, you select records that match that student number from the third file and make a list using the class numbers that come with the records.

To get all the students that have taken a particular class, you select all the records from that third file, matching on the class number, and that gives you a list of all the student numbers.

When you are talking about invoices and customers, that is a little different because it is NOT a one to many as it is in the example above. That is, one invoice does not belong to many customers - that is a one to one. If you have an invoice file and a customer file, You just used those two files and look up invoices matching on customer number (that you have as part of the invoice record).

But if you want to know what customers bought a particular product, or do all kinds of interesting cost/sales analysis of products, then it gets more creative. Then you have one customers buying multiple products and one product with multiple customers. That’s like the students and classes. Also, you can design it so each invoice line time is a separate record - in which case you’d select invoice records matched on product number, then create a list of customers from those invoice records.

If you have one invoice record with a fixed number of line times as fields, then you have to select invoice records by looking at each product number field in the records.

Note that, as in our student/classes example above - you could created one record as an invoice “header” that has customer info, dates, maybe invoice total, paid or outstanding, etc. but the line items could be a separate file that simply lists the product, quantity, price with the invoice and customer number it is tied to.

Other things to keep in mind are price and customer address changes over time. Some people mistakenly only keep price as part of a product record. When they pull up an invoice, the price is brought in via a “lookup” from the product file. The problem with that is, prices change. So if the product has had a price change, and you bring up an old invoice, you want to see the price at the time of the invoice, not necessarily the current price of the product. So you have to pay a little attention to timing - when things happen. in the example just mentioned, you could not do the lookup if the price field or customer address field in the invoice already has content. That way you won’t replace historical information that should stay put. Another way is to move finished invoices to sort of an archive file that allow you to search them but doesn’t have all the auto lookups and replaces that might happen in a “live” invoice.

It’s great fun. I’m sure you will find all the assistance here that you need to duplicate, very closely (but not necessarily completely or exactly), what you have in that other database.

1 Like