150 field limit

I don’t use the data sheet in my Form 1065 file as it has 7 separate pages to display the fields. However, the file has 304 fields. Is that a problem?

Panorama X and I believe macOS are optimised for databases that have few fields and many records, resulting in super-fast processing times. When I converted a database with 250+ fields from Panorama 6 to X, it took minutes to produce reports, much slower than 6 in fact. Then I changed the PX database structure to less than 50 fields (I think about 10) and many records (tens of thousands), and it processes the same reports in a few seconds.

If you are talking about tax forms, if I recall, many fields are “summary” calculations of other fields, Perhaps those intermediate “fields” could be variables. Or - and admittedly more work - separate databases with their own “form” output and the necessary results passed to other databases.

It would take more work to set up, but once it’s done, you have the advantage that if a page changes in the future, you wouldn’t have to deal with a large multiform database to adjust it.

Side-car’ng on what ddd said, in yesteryear, customers would come to Panorama from Excel - spreadsheet orientation. Because of that, they tended to make a field for a time period (date) and the database rows would be categories (income, expenses). So the datasheet in Panorama looked like their Excel spreadsheet. Problem was, with each new “data point” their database grew in fields, not in records. As that great philosopher, Gary Larson, would say, “Trouble Brew’n”.

They eventually learned to make the categories database fields and add records for the data points (dates). Panorama could take care of the reporting requirements, or they could export what was needed (usually summary information) to a text file for import back into Excel for the report layout.

First of all, if you don’t use the data sheet, the limit doesn’t apply. The limit is only for the data sheet. Though you must have used the data sheet at least to create the fields in the first place.

Secondly, you say you have “7 separate pages” to display the fields. What does that mean? Do you have 7 separate forms? Are you using tab panels? Either way, it sounds like you would have only a fraction of the fields on any particular form.

Since you’re asking if there is a problem, I’d say that means there is no problem. As you add more fields, performance can become an issue. In the data sheet, this mostly shows up as slow performance when inserting and deleting fields. In the past, a few users have used 300, 500, even over a thousand fields. Then they would contact us wondering why it would take 30 seconds or more to insert or delete a field. Note that the database itself can still work fairly fast even with a lot of fields, the problem was the Apple API we are using to display the rows and columns. This is an API called NSTableView, designed for tabular data. For example the Finder uses this API to display files. Apple designed this API to work really fast even with tens of thousands of rows, but they didn’t seem to care much about performance with a lot of columns. In the past, performance would start to degrade quite a bit when you went over 100-200 columns. If you’re using a more modern computer/macOS then you can use more fields without much degradation. This is both because the new Apple Silicon chips are much faster, and because Apple has made performance improvements to the NSTableView API. Since there are still many Panorama users with older computers and older operating systems the default limit has been kept at 150. If you have a newer system you should be able to increase the limit, or if you’re willing tolerate slow speeds, you can increase the limit on older systems as well. It’s up to you.

So basically, the 150 field limit in Panorama is basically a speed bump. In the past, users had no reason to think that adding hundreds of fields might be a problem, and some users would do this without thinking about it. You can still add as many fields as you want, but at least you’ll have seen a warning about what the performance implications are. See this help page to learn how to increase the field limit.

Forms will also get slower the more items you add. Again, the exact effect depends on your computer. If you find that a form is too slow, you should probably split it up into multiple forms, perhaps using tab panels.

This precisely applies to me, except I started with Overvue. (Then came “The database that thinks it’s a spreadsheet” !)

The fewer-fields more-rows model was far superior to my earlier spreadsheet-style effort. It is much simpler, faster to code and easier to maintain over the long term, as well as producing reports from forms much more quickly.

Greg, I have a lot of experience with this topic - three weeks ago I went live with the medical office management suite after waiting 5 years for processor speeds to catch up. In the interim I tried many schemes to speed up the responsiveness of different pages and found it matters to think it through and try different approaches.

Most of my DB’s are lookups with only a few fields, but the heavy usage Patient Info and Transactions are both over 300 fields wide with 10’s of thousands of records. I have found that a few tricks make a big difference in performance.

  1. Put the most used fields to the left side of the datasheet so they are found more quickly when searching or sorting. Once a record is found, getting a field’s content that is far to the right is very fast. But if searching 75k records for an element that is 300 fields in will make for slower response.
  2. Every time the data is sorted or searched, leave it with the most recent ones at the top if they are more likely to be used again. I’m more likely to look at a patient’s file if I saw them last week than if I saw them 20 years ago. This makes a big difference in drawing the calendar, which has 7x48 appointment slots x 3 flags, or over a 1000 lookups every time we move back and forth. Every time a patient account is looked at or used, the DateLastAppt field is updated and the DB is resorted.
  3. Different Pan elements work differently and I find it worth the time to try different schemes and time them. For the calendar, for example, do I do all the lookups one at a time, or build an array and put them into the form. I found it mattered if I made 48 arrays with 7 elements vs 7 arrays of 48 elements vs 4 arrays of 336 (one for each type of info) - I settled on a hybrid. Text list vs matrix vs text display made a huge difference in usability. I’ve tried them all at one time or another. Speed is my main goal for all of these items.
  4. I just revamped a matrix for reconciling payments in a person’s account that involves clicking on checkmarks and changing the color - green is good, pink is unverified, and blank is not yet examined. I got a major boost in speed by switching from a matrix to a text list with custom coding to allow individual clicks acting as if they were active buttons and update the checkmarks and payment amounts.

These are just a few ideas that might apply to your situation. The bottom line is I have made it work with really wide DB’s that are in use all day, every day.

1 Like

Going from Overvue to Pan 6.0 my main database gradually increased over the years to over 1600 fields! Most fields contain raw subtest scores from dozens of dementia assessment tests, which math-based procedures convert to percentiles which get displayed in graphic charts in a 7-page (7 Report Tiles in a single Form) patient report. Boolean procedures generate hundreds of lines of text based on the scores, in paragraph Text Displays in the reports, which is stored in other fields. The Form generates a PDF neuropsychological report that is can be emailed or printed and faxed/mailed to our referring neurologists and doctors. Each patient is a record in the DB with tons of test/demographic data stored in up to 1600 fields. Our psychometrists test and enter data in patient records stored on a Pan X server so supervisors, billing staff and administrative folks can all access the records. Panorama has served us for ~25 years allowing improved efficiency so we can continue accepting Medicare rates for everyone ≥65, which are now lower than Medicaid. Most traditional neuropsychologists (along with nearly all psychiatrists) have quit Medicare and, sadly, gone private-pay given Medicare’s low reimbursement for the onerous, time consuming work in scoring tests and report writing.

Our DB’s size sort of freaked out my consultant (James Cook) and with his encouragement I’ve gotten it down to 800 fields, currently. Even with 1600 fields generating a report, with thousands of lines of code (much unwieldy given my programming limitations) in multiple procedures, took only 10-15 seconds due to Pan X’s unique ability to keep a full copy of the DB on each client machine; i.e., no back-and-forth contact with the server required. All procedures are run locally on the client machine now in 64-bit mode on Apple M chips. So having a huge number of fields on a server-based DB caused no detriment.

The only down side I’ve seen to having so many fields is Pan X is the insert <+> new fields command in the Data Sheet view won’t work. But, I can add +>> them to the extreme end of the Data Base’s list of fields and then move them around with the Reorder Fields window from the Fields menu, which is a bit tedious but doable. Another thing I had to get used to is Pan X recommends having no more more than 150 field columns showing in the Data Sheet view, and limits the max. to 500 visible. But, there is no limit to how many fields you can create, nor as to how much data is stored in each of those fields and finally, unlike 6.0, X also allows unlimited amount of code in a single procedure window.

With a couple thousand records, each with 800 fields, I’m willing to wait 10+ seconds to run thousands of calculations and text-related procedures to generate a multi-page, thousand-word report. I guess I could get that down to 2/3 seconds by paring out a few hundred more fields, but I’m just not in that much of a hurry. I do limit the number of records by periodically deleting them after copying them to an archive DB, but that archive (with thousands of records and up to 1600 fields) still seems to work fine – as record searches, selections, etc. still take only a second or two. In short, I’ve seen no limits nor significant compromise in DB function due to too many fields, amount of data stored in fields, too many or too complex procedures, and/or having too many records in a Panorama DB. Even in extreme use, it is fast and has shown no limitations for us. We did suffer a time of weird, long beach ball spinning slowdowns, but that turned out to be a hardware problem, and not due to Pan software. Amazing! Don’t fear having too many fields.

With a couple thousand records, each with 800 fields, I’m willing to wait 10+ seconds to run thousands of calculations and text-related procedures to generate a multi-page, thousand-word report. I guess I could get that down to 2/3 seconds by paring out a few hundred more fields, but I’m just not in that much of a hurry. I do limit the number of records by periodically deleting them after copying them to an archive DB, but that archive (with thousands of records and up to 1600 fields) still seems to work fine – as record searches, selections, etc. still take only a second or two. In short, I’ve seen no limits nor significant compromise in DB function due to too many fields, amount of data stored in fields, too many or too complex procedures, and/or having too many records in a Panorama DB. Even in extreme use, it has shown no limitations for us. We did suffer a time of weird Long Beach ball spinning slowdowns, but that turned out to be a hardware problem, and not due to Pan software. Amazing. Don’t fear having too many fields.

2 Likes

ScottC - I thought the above sentence would say … a field’s content that is far to the left is very fast. But maybe you meant, once on the desired record, even if the field is far to the right, it is very fast - unless it is way (over 300 fields) to the left.?

Because you mentioned patients seen last week vs patients seen 20 years ago … do both have to be in the same database? T’were it me, I’d consider pruning out “non-current” patients by moving them to an Achieve file. If someone “not-new” comes in, and isn’t found in the current file, I’d open the archive file, search for them there, and move their records over to the current file.

There are all kinds of ways to speed things up. It used to be a “thing” in yesteryear - back when it was a challenge to fit code (Pascal) procedures in 32K sections. But these days, with GB’s of RAM and GHz CPU speeds, maybe the differences aren’t worth chasing.

I meant that if your desired matching field is 300 to the right and you are searching 75k records, you might notice it being slow. But if the field you are looking to match is close to the left, it will find the desired records quickly and then the desired cell - for one or a few lookups - will be very fast even if it is 300 fields to the right.

Not sure how important this is with modern setups, but it used to be a pretty big deal. I always set up my DB’s this way to this day because it can’t hurt. But as I noted in a prior post, the speed increase when moving from an M1 to an M4 iMac was very noticeable, I still work to make things as fast as possible when dealing with large datasets.

I have toyed with the archive idea many times, but there are too many accounts that go back many years and people that pop back in for some care after 10 or 15 years, that it seemed too cumbersome. If old records are truly just historical, then archiving would be a good idea if speed was suffering, but I think most of the time it’s just not worth it.

You know the concept of moving or deleting emails? When Apple started saying let them pile up and Spotlight will find them, I went along with it. Even with 100k emails I don’t notice a speed hit of any kind in Mail.

If I’m understanding you correctly, you’re saying that there are elderly Alzheimer’s patients that have gotten care they wouldn’t have otherwise received because of the work I’ve done on Panorama. If that’s true, that’s so gratifying to hear. I’ve put in a lot of long hours on Panorama, and stories like this make all the hard work seem worthwhile.

3 Likes