X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Requesting A Little Help With Importing, Any Version    
post Mar 28 2019, 01:47 PM

Posts: 7
Joined: 28-March 19

I am currently using an inherited old Filemaker runtime db for my small business. Its a simple invoicing system with a few thousand records.

1. Am considering recreating it using Access. I have some exp with Access (from the old days in IT) and dbs in general. Shall we say a bit rusty.
I'm having trouble getting started with importing my records.
Am sure this kind of question has been raised before but can't seem to find quite what am lookin for without going into exhaustive Access tutorials etc.
So, forgive me for that.
They have been exported to excel ok.
Am stumbling over this:
There are unique invoice numbers that I want to preserve. Starting at i.e. 39622...And want this to continue to increment in the new db...from the last highest
If I use auto number in Access, it wants to restart sequence.
Not even sure that the invoice field should be used as a primary and ID field. Have read that IDs should be used behind the scene for linking etc.
So how to get existing invoice numbers imported as is (there are some missing..which complicates it more)
and continuing to use them as unique ids ..auto incrementing etc..
Should the invoice field be set as primary key and incrementing(autonumbering)? Or should another field be added to do this?
If so, then how would the invoice number auto increment? VBA? Can it also autoincrement ?
Seems like it shouldn't be too hard to do. But looking for a sound way to do so.

Go to the top of the page
post Mar 28 2019, 01:52 PM

Access Wiki and Forums Moderator
Posts: 75,724
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

There are lots of ways to tackle your situation. One I would probably employ is create new tables to the specific structure you want (possibly a better one than the one you already have). Then, link to your Excel files and append the data into your new tables. If you created an Autonumber PK in the new table, you can assign the invoice number column into it during the append process, and you should get what you want (preserve the old value and auto increment the new ones).

Hope it helps...

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
tina t
post Mar 28 2019, 04:25 PM

Posts: 5,988
Joined: 11-November 10
From: SoCal, USA

one note re autonumber as invoice number. there is no way to guarantee that there will be no gaps in the invoice number sequence. if you don't care, no problem. if you do, suggest you use Autonumber as the table's surrogate primary key, and use a separate field for invoice number, generating it at the point of data entry, using VBA.


"the wheel never stops turning"
Go to the top of the page
post Mar 29 2019, 08:45 AM

Posts: 7
Joined: 28-March 19

Hey thanks for that. Yes I read something about using autonumber as an incrementing invoice number Or not.
At this point am still in the re-discovery stages and that makes sense what you say.
I did get the autonum to restart though! After a bit of effort.
2 stage, 2 query process. An append seeder query with 1 entry to start the number seq. Second query to append the main data. It worked!
There were deleted invoices in main data...so there were gaps and I thought it might have messed up. But it didn't. It brought in all records
gaps and all and maintained autonum..tested with a new rec entry etc... Interesting. But I wonder if its safer to do it as you suggest.
Would that just be i.e a click event on a new record to just do i.e. invoice = invoice + 1 ...kind of approach?

Sorry this is a bit long.

So, that said, if I may pick a few brain cells re design...Tell me what you guys think...(To an ex IT guy..with "some" programming).

1. One Table? I'm thinking of just using one table to keep it simple.
I exported everything from FileMaker to excel. 1 file. NP. I can't see the old table structure cause its runtime. Can't get pass..
Am not planning to import everthing in the new system. It goes back to about 2007. About 14,000 entries.
I've been here for about 2 yrs with about 1500 new entries. So I might just import that and use old system for ref.
The form in FM is very typical invoice, but its simple. Prob maintain that.
See attached. About 10 fields for customer info on top. 14 fields each in body for QTY, Lineitems, Price etc and calcs subtotals..totals below
We don't have a huge products listing to go through. We just know it. We are (me and son) a vacumm store. Machines, hoses, bags etc.

So would I even need to breakup cust info with qty, and line items etc into sep tables?
Or just keep everything as is in one table. Each record with its own invoice number has its own cust info, items, price etc anyway as is.
Fairly simple. Would it even need a primary key?
If so where would invoice number go..which table..with cust info I'd think.
I guess for future it might help to have it separated. Am considering having some products as a dropdown pick right in the invoice. That be nice eh?
But I could just add that as a new table?

2. Main functions of course are Search, New, Print, scroll thru etc. Need this of course.
The search button now simply allows to type in any field and find 1 or more records assoc with that. Its broad, simple and fine with me. Need to maintain this.
Would a 1 table thing affect this?
I don't need to generate reports or much like that..Its sep from our POS.

3. Ok 1 more. I played around with simply using the jpg image blank invoice as attached as the form background itself..If I keep it same..Wanted to keep the Warranty section
- not using right now, maybe in future - and keep the color..basic format etc..I can just line up the fields
with the form items..It seemed to work..Mostly. Printing may be a bit funny with alignment. You think its worth chasing that..? Or is that a rabbit hole?
Prob just redesign?

Much Appreciated;

Attached File(s)
Attached File  TNV_Invoice2.jpg ( 186.29K )Number of downloads: 8
Go to the top of the page
tina t
post Mar 29 2019, 12:01 PM

Posts: 5,988
Joined: 11-November 10
From: SoCal, USA

There were deleted invoices in main data...so there were gaps and I thought it might have messed up. But it didn't. It brought in all records
gaps and all and maintained autonum..tested with a new rec entry etc... Interesting. But I wonder if its safer to do it as you suggest.
Would that just be i.e a click event on a new record to just do i.e. invoice = invoice + 1 ...kind of approach?

an autonumber is generated each time you begin a record. if you press Esc key to wipe out the record before it's saved, that autonumber is not re-used. result = gap. if you Append one or more records, using a query, and cancel the Append - the autonumbers that have been assigned are not re-used. result = gap. it may be possible to prevent that if you use a transaction, and roll it back before committing. i don't know, because i've never needed to try it; i use autonumber to generate surrogate primary keys, and nothing else.

in older versions of Access, you could reclaim an assigned-and-then-abandoned autonumber by compacting the database - as long as you didn't use an autonumber after the abandoned one. i don't know if that still works in newer versions of Access.

i think accountants (and auditors, maybe?) generally frown on missing numbers in a sequence - invoice numbers, check numbers, PO numbers. in a paper world, a numbered form that is "messed up" would be voided, and kept with the used forms, not thrown away. anyway, it's up to you, hon - it's your business process.


"the wheel never stops turning"
Go to the top of the page
post Mar 29 2019, 01:19 PM

Posts: 7
Joined: 28-March 19

Thanks Tina
Yeah the FM db am using...(prob created about 2004!) There's 4 buttons in header area that we use most commonly. Exit, New, Find, Show All, Print.
As soon as I click New, a new blank invoice is started with next invoice number. No backing out.
So I don't know how this is done behind scenes. If its by autonum or code. But I hear what you are saying.
If I can maintain this and a few other things like import, export, I will be happy.
K. will play around with options. So you suggest using a PK field..i.e. RecordID and code something for invoice incrementation.
Would you say something like Dmax would be a way to go? Not sure exactly yet. Have to play around with it.
Go to the top of the page
tina t
post Mar 29 2019, 03:19 PM

Posts: 5,988
Joined: 11-November 10
From: SoCal, USA

you know, i never asked: why are you wanting to move to Access? sounds like you're pretty happy with the application you've already got. don't mean to sound snotty - it's a real question. what functionality is lacking in your current app, that you want to add or expand by switching to Access?


"the wheel never stops turning"
Go to the top of the page
post Mar 29 2019, 03:25 PM

Posts: 3,171
Joined: 27-February 09

I agree with Tina... What if you kept using the FileMaker Db (if it ain't broke...) and maybe linked to it in Access if you wanted to write queries against it. (I haven't used filemaker since pre-history... like 1996!!) So I don't know how it handles querying these days. It used to do everything with scripting, which was a huge nightmare.

So what questions are you trying to answer from your FileMaker database?
Go to the top of the page
post Mar 29 2019, 04:08 PM

Posts: 7
Joined: 28-March 19

NP re as to why. Funny you ask.
Well for starters the pdf / invoice I included has my header, logo. The original system has the previous company owners. Different name, various things.
I changed company name. Long story but no choice. The price was right so I wasn't too concerned.
I can't modify it. Its runtime, pass protected..Can't get a hold of orig programmers.
I don't think I can hook into it from outside with queries or anything.
So what to do....
I found a workaround using a small app called classic pdf editor which allows to easily size an image over top of another in pdf.
So I had one created.
When I process an invoice, I have to print to pdf, overlay my image, etc etc.
Its not too bad but it results in having a file for each invoice processed. Its ok for now but if I have a banner year and I'm processing thousands..then it might get to be a problem.
Then again, that's not a bad problem to have !

But was also thinking of a redundant system..or something for the future. If i.e. it all blows up. Its pretty old. But I do
backups every night. And its small..portable..the exe..a few files and the db file itself. That in itself is good
Plus kinda liked the technical idea of redoing it. It shouldn't be too difficult I thought.
This was for both replys btw. Thanks

Go to the top of the page
post Mar 29 2019, 04:19 PM

Posts: 3,171
Joined: 27-February 09

So no way to get back to the original developer?
The hassle is that I think the folks at FM can get into the database, but I don't know what the restrictions are for that. (I worked for them like 20+ years ago, back before pollution was invented). You may need permission from the original developer.
Go to the top of the page
post Mar 29 2019, 04:33 PM

Posts: 7
Joined: 28-March 19

Nope. Tried. This was done years ago. What's chances they even have the password for it anymore.
Go to the top of the page
post Mar 29 2019, 04:45 PM

Posts: 3,171
Joined: 27-February 09

Did you call support and ask them? The kicker, IIRC, is that you need permission from the original developer.
Go to the top of the page
post Mar 30 2019, 08:09 AM

Posts: 7
Joined: 28-March 19

Another reason to migrate off my FM runtime. Just remembered this. There were some issues about a year ago like searching for "Mary", caused a total program crash. Every time.
I ran a compact db at one point (of course I backed up first) and it deleted a bunch of legitimate records! Yikes!
So, just one more thing that makes me think this could be a bad design and a bit edgy about long term use of this.
I fixed it, trial and err, by exporting all to csv, creating a structure only copy of the db and used that. An Empty db. Reimported the csv and voila.
Mary is happy.

I gotta think there's standard practice..use..method for proper use of unique ID (PK, autoincrement field) and something like an invoice field (right next to it shall we say) where it too needs to be
unique and incremented. This must be common. Something that's not too complicated.
For me, this is single use. No network sharing

Go to the top of the page
post Mar 30 2019, 12:03 PM

UtterAccess VIP
Posts: 2,825
Joined: 12-April 07
From: Edmonton, Alberta Canada

Well, if the application does not have a lot of forms, and features, then I see no problem with jumping in, and re-doing the application in Access. It is a great tool.
However, to do this correctly, you will need SEVERAL tables. You cannot make this work with one table.

I would consider getting your hands on a FileMaker developer, even if to JUST export the data into Access. There are number of conversion tools, or even ODBC drivers.

And you can likely buy a copy of FileMaker for less cost than one day of developer time. Having a working version of FileMaker would allow you to export the table data (say using dBase format), and allow you to get out all tables. And FileMaker has a Multi-value type of column, and these can be a pain to export (just like they are in Access).

Invoice number:

The general approach used in Access is to build your own custom invoice number system. Now, the term “system” is quite a fancy term.

What we do is create a table. Say, call it tblNextInvoice.

The table would look like this:

This table when viewed as data sheet would look like this:

So, our next invoice number will be 12345.

So, it is a simple table with one row. Its only job in life is to allow you to track and set up the next invoice number.

So, in above, we can enter the starting invoice number we want.

Next step is to build a wee bit of Code that will return the invoice number (and then increment the number to the next value).

For this, we add and standard code module, and we have this code:

Public Function NextInvoiceNumber() As Long

   Dim rst     As DAO.Recordset
   Set rst = CurrentDb.OpenRecordset("tblNextInvoiceNum")
      NextInvoiceNumber = rst!NextInvoiceNum
      rst!NextInvoiceNum = rst!NextInvoiceNum + 1
End Function

Now, save all the above.

In the debug.window (ctrl-g), we can now run + test this invoice system we built. I can type in ? <expression> to test out our code.

The debug window will thus produce this:

? NextInvoiceNumber
? NextInvoiceNumber
? NextInvoiceNumber

So the above is how you can test your code.

So each time we use our new function, it will return the next number for us (and increment to the next number).

And we could I suppose add a few extra columns to our invoice table, since we might have some complex invoice number system that includes a few characters, and perhaps even the year. (But, we save these issues for a later time). The point here is we can now create and produce any kind of invoice number we want. We as noted could even add some letters, or month or whatever. Again, the beauty of this approach is we can build any kind of invoice number system – perhaps even one that starts at 01 for a given month.

Ok, so now we have an invoice number system. It allows us to enter (set) the starting number, and we have some code to grab + get this number.

Now, when we eventually build some kind of invoice form, and have some button, or ability to add this new invoice, we can have that form call + use this routine, and stuff the result into a text box (column) of our database (the column called invoiceNumber) in our invoice table. (And YES, you will have a separate table from customers for the invoice(s) you create.

And of course, now that we mastered the above concept, then we can use this new found knowledge to build say customer numbers, perhaps even print out bar code labels that we scan for parts that have in your inventory. (So you can start to automate your parts, belts etc. that you have on hand. Now when you write up an invoice, you might use it for the work order, and it will TELL YOU that you don’t have that filter, or belt, or whatever in stock, and you have to order what you need. Now Access can produce a list of “things” that you need to order based on you week of up-coming work).

So, the beauty of using Access is you can start with JUST invoices, but because YOU get to choose what the application does, then over time you can add and extend new features into the system. The ability to add things and features (your business work flows) into that system will 100% depend on USING MULTIPLE tables related in the correct fashion.

And note that you invoice number likely will not be a primary key. The fact that you entered some customer number, or invoice number should not prevent your database from working.

Just because we don’t have some address, Invoice num, or whatever that should not matter.

In one of my invoice systems, we are allowed to have many open invoices, and we actually assign the invoice number when we print the invoice so, my print button calls that above invoice number routine. So, again, your system should work just fine with, or without invoice numbers. And as noted, you might use this system to produce a list of work you have to do this week. And as noted, it could also produce a list of parts etc. that you may need to order to get this work done. So now, you might just work on work orders that you KNOW you have the parts in stock, and wait for parts for the work orders you can’t work on today.

Regardless of future ideas, I going to say this again:

At the end of the day you not going to make this work without multiple tables.

So, you need a correct relational table design.

You might be able to use the table analyzer to split out your data into these multiple tables. So the built in table analyzer may well split out the data for you.

So the first problem of course is breaking up your imported data. (Or you leave it in the older system, and setup the new system with a list of customers, and then just add invoices from that point on. The reason for this is splitting out your old data into the correct new table(s) can be some work if you are new to this.

You simply CAN NOT use a single table design to deal with your problem, nor can a single table re-produce the features you need.

I mean, in the old system, you ONLY enter a customer one time. Yet in your exported data, the same customer appears over and over. You really (but really really really) do NOT want this in your new system. You enter a customer ONE time, and then re-use that customer. While this suggesting does not seem that important, it MOST certainly is.

You see, by forcing and adopting a design in which you ONLY enter the customer seems like not that important. So what if you enter the customer again? (That is more work).

However, this is NOT really about more (or less) work. If you correctly adopt and design a system in which you ONLY enter the customer one time, then all of your data from day will be “related” and attached to that one customer. This is FAR more significant than saving you data entry time. This design means that any sales, any invoices, and any other work order, emails and ANYTHING you do with that one customer is NOW attached and related to that one customer. This thus builds up a customer history.

You have a customer. When did you last see that customer? How much do they owe you? What is the status of their existing order? So, now you have a computer in the back room that can display + show the work and task at hand. And now when you in the back work area update the work order, then the part time summer student you hire at the front desk will INSTANT know that the order is ready. And then the system can produce a list of people for that person to call on the phone and tell them that the order is done and ready for pick up. And if they phone your business then the front person can look at the computer screen, and check + know if the order is ready for pick up (as opposed to running into the back room and asking if such and such vacuum is ready for pick up). So, Access is multi-user right out of the box, and you can “enter” information about the order, when/if it is ready, what parts you used (or need and are on order).

So now, the front person wile on the phone can check the order and see that parts ordered have not yet arrived.

I can go on and on here. And adopting such a system brings much order to your business – you spend more time doing real work and being happy with reduced pain points in your business. And that makes customers happy.

I am simply pointing out that if your data model (your table designs) are wrong, you CAN NOT do all of the cool ideas I am floating above.

So, make sure you build a correct data model, since then all data and all actions are tied to ONE customer record. If you don’t do this, then inquiring into that system can’t group data and information to ONE customer.

So, you likely to have

tblCustomers (we can use the ONE customer over and over for each invoice).

tblInvoices - this is a table of invoices, (realted and attatched to table customers).

tblInvoiceDetals – this is the repeating rows of data (invoice detail).

And if the old system had features to look up (select) a service such as new belts, or filters etc. from a pick list, then again this will required additional tables.

I would consider asking in a few FileMaker forums as to what is the best way to export that data. You either:
Just import customers, and start new invoicing in your new system.

Or, you attempt to import the history data into the newer system. For this, you need the table(s) from the older system.

Access is not hard, but one has to drop the concept of Excel spreadsheets, and start thinking in terms of related tables.

This concept tends to be the greatest challenge in using Access, since while “many” have seen lists of data, and many have worked with Excel, the idea of related tables tends to be a new idea, or at the very least a strange idea and concept.

It is this multiple table’s concept that un-locks the ability of Access say compared to Excel. And it is multiple tables that allowed the older system to do its magic.

If you want similar features, then you have to adopt a similar set of related tables. Thinking of “one” table is a non-starter here. As noted, FileMaker has “many” types of multi-value selection fields that in theory will result in additional tables in Access. And if the FileMaker application was from an “old” version, then the tables tend to be even less SQL friendly.

And, as for layout of the report (invoice)? I STRONG suggest you re-create the layout best you can in Access, and not attempt to use some background image. You might insert a few images for company logo etc., but you want to stick to the built in design tools for the build of the invoice.

It not clear if you are to print out on existing paper invoices, or if you want Access to re-create the whole invoice for you at print time. (Either choice is possible). However, if you can get Access to print out the invoice with the layout lines and grids then that is my suggestion.

Using access to layout the invoice will allow creating of PDFS, emailing at the touch of a button etc.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Apr 3 2019, 05:56 PM

Posts: 7
Joined: 28-March 19

Hey Albert;

Thanks for taking the time. I will take your advice and approach for sure.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st July 2019 - 07:54 AM