Importing previously imported file to scavenge new entries


#1

As a music teacher, who teaches all the students in the school, I periodically need to go back to our server-based commercial database to “scavenge” newly enrolled students.

I go to PowerTeacher online, export the school roster as an Excel file, convert it to CSV format, then…?

I want to just import the new students in the school (new since my last import of the school’s database). I can imagine some formula where the full name (or the students’ ID numbers) in the school database is compared to the full name (or IDs) in my PanX database, then those duplicates are not imported.

Or a longer process might be: Add a field during import with some entry to mark the imported records with their import date, then show all the duplicate records (based on the Full Name or ID# field) who have the new import date, then delete all those; then, for all practical purposes, only the new students’ records would be added.

Eeven more preferable would be to additionally update the fields in my database with any updated changes (such as a phone or email change), on the school’s database (but that’s probably best left to a separate post).

So, I have some processes in mind, but I’m not sure how to implement them in Panx, or what the best method is based on others’ experiences. Would appreciate some guidance. Thanks!


#2

Or the simplest approach may just be to replace all the old records with the new ones. Call it File A. Keep the additional information for your use in File B. Use the Student ID as a key field in both files. When you import to File A, your procedure for that could check for Student IDs that are not in File B and add records for the new Student IDs.

Once you have this, you can look up the information from File A in File B. File B will then be updated each time you import the data.


#3

Alternatively, import all of the up-to-date data into a new file and do a ID lookup of the old file with “New student” as the default value in the lookup( function - where no match is found, new students will be flagged with the default.

BTW, it’s not difficult to write a Visual Basic macro to open the new Excel file and save it as text. Your Panorama procedure can open the Excel file that contains the macro, then import that text file - everything runs seamlessly at the click of a button.


#4

Thanks for your replies. If I understand them correctly, both approaches are similar and involve keeping a separate PanX database that essentially mirrors (at a given point in time) the school’s database.

I wrote a lookup statement:
lookup(“TEST”,ID,Id,Status,"")

In English it looks in a database called “TEST”, in a field called “ID”, and compares the value to the value in a field called “Id” in the current database. When it finds a match it gets the value from the “Status” field in the “TEST” database.

It works great when used to Morph a field using “Fill with Formula…”

I had hoped it would work as a field formula in the Formula tab of a field’s properties, but used that way, the formula has no effect on the field values. (Am I using the formula, or the formula tab, incorrectly?)


#5

The formula is triggered when you make an entry into a field that is included in the formula.

If you were to manually enter an ID into the Id field it should trigger that formula, in that one record, but it won’t be triggered by import of a record containing an ID, and it won’t run automatically in response to writing the formula.


#6

Thanks for the tip @dave. Sometimes I feel like such a noob! I guess that’s because I am :blush:

To this point, then, I know of no simple and “automatic” way for new students to be flagged in the “mirror” database of the school’s server database. However, if I import the school’s records, and then click in the “Id” field and click out of it, then the formula executes in the “New Student” field, but it would obviously be quicker to use the formula in a Morph Field with Formula action, which is a small step only needed about once or twice a quarter.