I spoke to quickly on the region preferences. The date series was supposed on Oct 1985, ie. 10/85. But the error is saying no field named “1/85” When I wrote:
let first=date("10/1/1985")
I was thinking US style, Oct-1-1985. With your region setting it now meant Jan-10-1985 so is starting to transfer 419 consecutive months with “1/85” Change that to
let first=date("1/10/1985")
John,
It worked!!! I’ll have to check to make sure all the data which transferred is correct, but I know how I can do that, without too much difficulty. THANK YOU!!!
Could you send me that loop code to remove the 419 fields from my big db? Then I can just tweak the two dbs and I’ll be good to go, thanks to you. Thanks again.
Make sure you have adequate backups, but this will remove the 419 fields. Put it in a new procedure window of the 859 db (donor db doesn’t need to be running for this one) and run it there.
let first=date("1/10/1985")
for i,0,418
execute |||field «|||+datepattern(monthmath(first,i),"mm/yy")+|||»|||
deletefield
endloop
saveFor future reference, you can use the datevalue( function to specify date constants in a manner that is agnostic to system settings, so October 1, 1985 can be generated using this formula:
datevalue(1985,10,1)
The formula above will work correctly no matter what your system settings are.
There are three design areas that seemed to come up more often than others when I have had to “fix” database files.
First - and maybe ProVue is a teensy bit responsible - is the user is thinking horizontally (fields) instead of vertically (records). I’m guessing that’s because their introduction to data was a spreadsheet; like a pro-forma statement. They’d see months or years as column labels across the top and various categories of profit and cost for those months down the page. And ProVue would say something like, “The database that thinks it’s a spreadsheet”. So people would use that structure to hold their data - each day, week, month, or year would become a new field extending horizontally across the data sheet.
Instead, the categories should have been the fields with one field holding the time interval (day, week, month, or year).
As data is entered, you should see your database growing in records, not in fields.
The second area was duplicated data. I once had to correct a business address and the user wondered why it took so long. In that database, the address was entered in at least three different places - and I had to go through more than 20 procedures in each of at least five different files to find them all. And they were entered in three different ways - hard coded into a procedure, live entered into a field, and displayed via a flash art object. How much easier to have that data in one place - even if in its own database with one record - and just link to it when needed. If it needed to be changed, it only had to be changed in one place.
Another place this shows up is form is Duplicated (cue Gary Larson starting another “Trouble Brew’n” series. One form has editable fields for data entry, the second form has fixed fields for display. Time passes - the user needs a change on the form Just something little - how hard can it be. Except it needs to be change not on one form, but two.
As a general rule, try not to duplicate.
The third area is not seeing the usefulness of an intermediate file. A standard example is the classical - I want to see all the classes this student has taken or I want to see all the students who have taken this class. Usually I’ve see this attempted by creating a student record and adding a record that copies over class information. Hopefully the user avoid the first issue and adds a record for the student with class info instead of adding a new class field.
The same thing is happening in the class database - a new record (instead of a new field) is added for each student. But this often falls into the second issue as class info is copied into the student record, causing some data duplication.
One solution is to create an intermediate database that just has the student ID, Class ID, and Date. If the student takes a new class the student record (profile) doesn’t change - a new record is just added to the intermediate file. Now if you want to know all the classes a student took, you can select all the records for that student in the intermediate file and display (lookup) all the class names (and other data) via the Class ID from those selected records. Works exactly the other way - all the students who took a class - select the intermediate records for that Class ID, and display the student by the student ID in those selected records.
The above is students and classes - but the same structure comes up again and again. For example, “What products has this customer ordered?” and “What customers have ordered this product?”
A “half-issue” might be considering the difference between history vs current time. The user wants the invoice database to pull over product information either when the invoice is displayed or a product ID field is exited. But if a product price changes and you are looking at an old invoice, you don’t want those fields to update to the new product prices. You want it to stay a historical record of what happened at that time. And that can get tricky because if you say, “Don’t change anything if some field already has something in it.”, then you’ve sort of locked it. If you don’t want to have to clear the field first, perhaps you can modify your “lock” to allow a change if, say, the Option key is down on exit.
But mainly try to avoid growing the database horizontally, entering the same data in multiple places (that goes for duplicating procedures instead of linking to one), and consider the advantage of having an intermediate “linking” database to bring associations together. Again - not “hard and fast”, no exceptions. More like considerations.
Now this is all “old style” technique/technology and PanX - especially PanX 10.2 and the new server - will probably have alternate/better/sutomatic solutions to some of those issue. I was probably influenced too much by the more mature Twilight Zone - Outer Limits. The shows tag line was, “… We control the horizontal. We control the Vertical …” sometimes automatic is good. Sometimes it’s good to be in control.
John,
Great. Thank you for this. I do have several back ups, so I should be safe. Thanks again for all your help.
Yes, this was my ‘problem’ way back when I initially designed my db. I thought it was OK to keep adding fields and that I needed to be careful of how many records I added. Now I understand that it’s the other way around.
I will be looking forward to PanX.2 and the relational tools it will contain.
Gary,
I think John is right. I will need to get more familiar with PaxX before I jump right in to PanX.2. I’m not trying to push the envelope with the things I’m doing with Panorama.
We stopped using that marketing message over 25 years ago!!! We stopped using it precisely because it caused misleading expectations. Though obviously it was a message that resonated with people – they still remember it all these years later.
As the mind ages, paradoxically, the past is clear. 30 minutes ago, not so much. Now, let’s talk about Flash Audio 