Field Data Form Translation File Script

Yah, this is one of the big reasons I sent those files back to you, cause you have all this in your head from years of working with the data, and I shouldn't really be touching it or making decisions. I can explain to you how the database and conversion works, to help you with your decisions concerning fields and data separation, but inevitably its you who has to make those decisions.

On that note, I've been recently thinking about a good strategy concerning the appform: you simply create a Word file listing all the fields, broken down into different categories (reman vs. whole etc.). I could spend an hour going through all your emails, which are still in my inbox, analyse all the pdfs you sent me, and try to make heads or tails of it, when it should only take you about half an hour or less to whip up such a file. Since many of the fields duplicate between the various categories, you can just copy/paste quickly, so it shouldn't take you that long, especially since your head has been swimming in it for so long and you know what you want. You'll then see it right in front of you, you should already know pretty well how the database needs to be structured, and you can break up the fields into a clear structure. I would recommend the clicky stuff which needs to be broken up, to put those fields in CAPITAL LETTERS. Followed by an indentation of the planned breakup, like:

ROUGH SIZES

          1x2

          1x3

etc. For those fields you are still humming and hawing over, something like:

PRODUCTION - under consideration for division and conversion to clickies

For those fields which you merged, like Panel Sizes thrown into Sizes, you don't need to make any special comments about that, for the same reason you do not need to make any comments about "Vice.Pres. - " thrown into "Contact persons: ". Its all one and the same field, the data has been merged, and we do not need to concern ourselves with that anymore.

Once you have whipped together this Word file, we can both stare at it and discuss it, I'll have a lot clearer idea about everything rather than a gzillion emails with a gjillion scattered comments, and pdfs galore, and THEN I'll be able to stare at the fields in question, once I get your data all together, and start thinking about possible ways to rip apart the data.

overandout!

Ok this is the last one from last night. Just to clarify re: "sizes" fields, there will be Rough Sizes: Surfaced Sizes: which will be text fields and will include the stuff we were talking about earlier, then Sizes: which will capture everything else, like the panel stuff we just did.

--------------------------------------

As for your fancy S&R, making things all green and shiny, etc, I will leave that to your spectacularly fast self. Gonna finish the rest of the end of it soon, just gotta run out to the bank/post office/coffee.

Yah, I think it's a good idea to combine our skills and knowledge like this and send things back and forth. You do the preliminary conversion from rtf, I then go through it with my fine comb and apply my fancy s&rs and macros, then send it back to you. The colouring is for me because every records (mill) should have an address and a Contact Persons, and it just makes my work faster, and I can see things clearly and if anything is lacking. Gives me a good overview, otherwise its all spaghetti text which is more difficult to decypher and a greater chance I overlook something. I set it up to the best of my abilities and knowledge, leave < symbols in those places where I'm not sure, and then you can quickly take care of the rest, or tell me what to do so I know how to deal with such cases with future files. If we had a hundred times more data than this, I think we'd get into a good groove, hire a cheapo, and things would be a hummin nicely.

Once I convert though, all the formatting gets lost.

Oh yes, by the way, one of the procedures I applies was to clean up the data. As I mentioned before, combined words ,commas thrownintoth e wrong place , ,and stuff like that can make the entire database sloppy, and if I'm going to try and find new customers through this, its worth my time to clean up the data. Cause people just get a negative impression, even though this has nothing to do with the structure and functionality I'm setting up. I can apply my fancy s&r and get all this done rather quickly, but I think it should be your job to do the spellcheck, which I did for all the files up to now. With spellcheck you cancapture thos e merged cases etc. ,or where the comma is in the wrong place. It's just a small point but it will make the dbase look so much more professional and solid, otherwise you might find yourself making these slight alterations over the next few years, when it can get done rather quickly right now using Word's spellcheck. You just PageDown quickly through the document scanning quickly over the squiggly underlines. Don't do a full F7 spellcheck, but just a squiggly scan.

Besides, the correct placement of comma could very well be used as a field separator!

Over'n'

------------------------------------------

> twitch just registered at TreePlanters Database

---------------------

All right, that's three that have registered already, exciting times. No work on my part, heh heh. Now just to keep working on this dbase more (lots of work still) to make it a standard in the industry and help divert more and more traffic to my site.

Just yesterday one treeplanting company said it was a "sound investment" my charge of 50bucks to add their details to my Translation Jobs site. Turns out they got a lot more trees and will need more planters. On the other end, I'm charging 30bucks per planter who wants to be informed of jobs which open up over the summer. It can often happen that a company is falling behind schedule and needs more planters, and many planters might find themselves without work etc., or wanting to get out of a shit contract, so they'd love to get sms notification while on the block about new openings, and immediately call the guy, before anyone else, and say they will be there with three other people tomorrow. So I'm thinking its POSSIBLE I could make as much as five grand each summer between both ends of the spectrum. I'd send the smses from my mobile account in Prague, at 1.5Kc per sms, like sending emails to a group list. Zero effort.

Just looking for different ways to make cash and SO glad I've started learning this programming stuff, cause I can just get creative and make money in different ways, all the while diverting more and more and more traffic to my sites, pushing them all up in the rankings, heh heh

---------------------------------------

okay, exciting times as usual. Attached you will find I've done in Excel and need you to look at the red marked cells.

Some notes:

- the Final tab is what we have already online. I converted it and the alignment seems okay now. Perhaps you could check

- My First Sample should be the same as Final and you can probably just ignore it

- for the BCMills and onwards, the very far column is hidden, and where the Category and Region columns are.

- look for the red marked cells by scrolling down and across etc. Do the changes yourself, where you can, simply by movng the data into the appropriate cell. Note that the conversion script when berzerk sometimes and copied the record for a few rows below. Usually you can just ignore the duplicate records below, but if its a big company you might wanna check against the original .doc file. All the "new" fields (basically misspellings like "grading Agency" as opposed to "Grading Agency" etc.) are to the right of the vertical line.

- go through and spot check if you have time to make sure things have lined up properly. Much easier to Insert Cell and shift everything in a column Down at this stage than to try anything like that in Access, but possible in Access if copy everything into Excel and then copy back. I was pretty thorough as usual, but an extra set of eyes is always good.

- if you have any special comments for me, put a < character in the cell and follow your comments by that, and then by email inform me to look out for that (I can do a Search through all tabs at once).

So once you confirm and finalise this file, it is an easy matter to throw it all into Access and I'll send that big file over to you pronto. Then we can start to talk about ripping up the fields into smaller clickies. Still need you to make that Word file I suggested of all the fields, so I/we have a good overview. You can probably just copy straight from the pdf files and then copy/paste stuff once you have that in Word. As I already mentioned in the very beginning, I have a good program which converts from pdf into Word, which I use for translations. Its called PDF transformer and its great. So I can convert some of the forms for you if that'll help.

Getting there!

----------------------------------------

Yah, let's go slow on this, cause once it's in Access, certain manipulations of data are a lot more slow and tedious than Excel. Also, if some fundamental error is found, I can reconvert it from the Word files. For example, at some point I noticed that ; in the records were causing a problem, because the conversion procedure was using ; as a field separator. Meaning that if a record said something like "bob uses 1x3 planks; but he's thinking of using 1x4 planks too", that would become two records in a row, where the second one would start with "but". Obviously this would push all subsequent records one to the right for that particular row (mill). I caught a lot of these. To shift the data for several records to the left or up etc. is easy in Excel, not Access. Let's examine da bugs.

Attached you will find my amendments and comments to the Field file. Let's bounce things back and forth until everything is super duper clear and agreed upon before I proceed with the forms, which looks like it will be a real B1tch, but probably worth it for me, since it will impress future potential clients that much more. Anyone else would probably charge you an arm and a leg, but I would suggest I just go all out and get this as fantanplastic as possible, and then you could consider giving me some bonus or whatever once you're rolling in the hay. I've got enough to get off the island now and that is my first priority. Speaking of which, I'd need that payment within the next couple of weeks because I can only withdraw so much cash per day and I will need to spread that out over several days until I have around a thousand Euro in cash by the time I get to the ferry (planning end of this month).

Want an invoice for this?

Concerning my comments, keep in mind that I use a dark green background with light yellow text. Not sure if my light green comments will be annoying for you, but your red comments are okay. Obviously do not use dark green, as I might not even see it. I can change my colouring too. Let's bounce this file back and forth until we are really beally clear on things. This is an important stage, as is the finalisation of the Excel file. Once that's pumped into Access (which is an extremely easy process), there's no turning back! k

Ok I'm starting to find funny things, like sometimes the first letter of the postal code is not capital, or sometimes a contact name is lingering in the phone field and stuff. I've fixed a lot but want to look at it once more tomorrow to be super duper pooper sure. in the meantime here is the glorious, all encompassing, master list of fields, including clicky boxes. Pretty much everywhere there are clicky boxes there needs to be a text field as well. at the very bottom, after the line, is potential future growth, if we end up doing Transporters and Reloads. just so you can see what it might be. lalalalalal, beer.

--------------------------------------------------------

OK I'm sending back the fields doc, I think I've clarified everything. I've probably been using the wrong terminology . . . I answered your questions and put spaces identifying where I need a place to write "Other ____________" There's a note regarding the page break to identify different forms in Access, just an easy question for you and I will adjust it accordingly.

Okay, read through your comments on the Wordfield file and below and still I don't think we're on the same page, so let's keep working at it.

Clickies: is only YES or NO. You never enter data. The yes can look like a check mark in a box, and no can look like an empty check box. You click it on or off, and you cannot enter data. If you want a comment next to the field then that requires an extra field. Maybe for better clarity let's just mark each clicky field as light blue or something.

For the percentages we agreed it will be a text field and you'll just punch in the percentages.

For something like Shipping we can make it a DropDown list, which can be set to only choose one, or multiple selections (by holding down the ctrl or shift key). So lets colour that pink. That would probably be better than clickies and breaking Shipping up into a bunch of fields. So think of any other areas which would be better served as a dropdown list.

The point of the clickies is to make it easier to edit or fill in. Technically a dropdown list could be as suitable as a clicky section. I really think you should check out my translator application form to get an idea of the possibilities. Go to kenax.cz, Login link, my dummy account is username: kenax, password: kosman. Once logged in click on the Translator Application form near the top of the list in the centre. You can see how to add language combinations at the top, and then press submit, and how the language combinations appear in the box even before you press Submit at the bottom. Then how to choose various subjects, and some sections are a sort of list (dropdown). These are the various options. This form is designed for your convenience, so you should have a good idea now what is possible and what you want. I then make it according to how you want.

Am moving through the Excel files, addressing all your concerns about non-consistent display of words. Making notes in an orangey colour for you, but everything seems extremely straighforward. Haven't yet opened the files you sent today, have to get writing and will be working on the Reporter for the rest of today then also tomorrow morning.

Then you're possible missing something, cause I marked it red cause it wasn't straightforward for me. Again, everything to the RIGHT of the vertical line must either:

- the data within the cell must be moved into a cell on the SAME line/record but another field to the LEFT of the vertical line

- erased and ignored

- or the entire field must be moved to the left of the line, meaning that an entirely new field is created.

Yes, the names must be EXACTLY correct. In the conversion script, we defined certain field names. Anything NEW gets thrown to the right of the vertical line. For example, "grading Agency" is new, and an obvious case, where I simply moved the contents into the existing "Grading Agency". But the ones I left for you to the right of the vertical line were not clear to me. When you send the file back to me, technically there should be nothing left to the right of any of the vertical lines.

I went back through our emails, way back, last weekend and I'm positive we are right now at a level of understanding. I have one question which is new: about the clicky boxes, like for the "Species:" for example. In order for the conversion to work properly, every time Western Spruce-Pine-Fir appears it must look exactly like that, right? Like it can't be WSPF in one place, then Western-Spruce-Pine-Fir, then western spruce-pine-fir. etc etc. how finicky is it? Does it care about capitals?

Like I said before, computers are not intuitive. When you make a script it does EXACTLY what you say. So when it comes across "grading Agency", it does not say to itself, "Oh, obviously the person meant "Grading Agency", so I'll just do that this way". So exactly exactly exactly. You can do an s&r before the conversion process, which I've been doing, but sometimes I simply overlook little spelling errors etc., which I anticipated, and which is why I asked the program to alter the script so that all "newbies" get thrown to the right of the vertical line, otherwise they would simply get LOST and not process, which we don't want. So all such newbie cases must be dealt with, in one of the three options I mentioned above.

So, for the clickies, this is why I left this process until the end. We can deal with one column at a time. Copy it into Word, lets say, so it is a big table, analyse the data, and s&r to make each name exactly consistent, and then use the script to separate the data again. Like for example:

Now:

1x2, 1x3, 4ft

After, using 'comma' as a field separator:

1x2 = Yes

1x3 = Yes

1x4 = No - this will need to be figured out somehow, or perhaps we switch to the list/dropdown option

4ft = Yes

5ft = No...

So it's not as straightforward and we should not rush this, because restructuring everything later could be a major pain in the arse.

What happens if its not done properly? Like if 'box shook' doesn't make it to the master list of Products:, after the conversion is that word thrown out? or does it mess everything up? There are going to be products in the Products: field especially that are not on the list of clicky boxes. Will they automatically flow into "Other _________"?

The "automatic flow" is the creation of field names to the right of this vertical line. We really need to figure this out. Sure, we can create a field Other and just throw all the extras into it (manually), or create a new clicky field just for those new cases. Once this is all converted and set up, having an Other field for each section will make everything easy, cause if the clicky is not there, you just throw it in Other. If a newbie field becomes increasingly common, like metric data once you enter Europe, then I can add the new fields later.

I have provided the list of products on our form, but can't possibly provide a total list of all the wacky specialty items people make. this may eliminate the Endless Clickies Question for the Products: field anyway, I guess.

Concerning the Wordfield file:

- some boxes disappeared. Lets be consistent, so I know what you want as clickes. Lets use the colouring scheme I suggest.

Concerning the flow of the form, this is also important. If something is always the same, put it in a separate page break section so I know. As I wrote, there are several ways I can go about this:

- fixed form approach, two options

          - one very very long form with all the options, the form itself could look much like your Wordfield file. Probably the simplest and easiest option. Sure, it might take a little longer to load in your browser, but then you'd have everything there in front of you, and you can reorganise it later in Dreamweaver, like move a clicky up to a different section etc.

          - several forms, one for each category etc. Not a great pain, cause I can just Save As the form, erase some clickies, add some new ones. More clear to your while viewing/editing, and the same form can be used for viewing by the users. More compact and clear rather than one massively long form. BUT, if it happens that a company crosses into another category and you want to keep the company as ONE record, you'll have to go to the other form to complete the data, since the respective clicky or field is not available in the other form. Same record, but you need two forms to complete the data.

          - a dynamic form. Would have to figure that out. You would fill the info which is the same for all companies. Then you get to a section Choose Category. Once you do that then the form changes below (the data above still remain there) and you continue working downward. BUT, again, if a company crosses into several categories, with this system you're basically screwed because I'd have to redesign it or something, because you cannot just go to some different form to fill in the holes.

What I would suggest is the following:

Let's start with one massively large form based on your Wordfield file. Break it up into sections, imagining that is how the form will look online. This way we can get the basics down and the shit online asap. After that we can get fancier with dynamic forms etc. For the user, we could use the same form for them to view, and eventually move to separate forms to make things easier to consume. The bottom line is that the underlying table remains the same. You open up the table in Access and you see the raw data. Everything is there. The contents of the house. The different forms simply package the data differently, like looking through Windows in a house. Through each window you are looking at the same table, but you can only see certain things from certain angles. Looking throw the south indow, you might not see the table lamp in the southwest corner, for example, but its still there.

Well, hopefully we're getting closer on the same page.

---------------------------------------------

once I go through all this and make the adjustments I think we will be on the same page. As for exact exact words, I wasn't asking about the field name, but rather the words in the field. For example clickies/drop downs, whatever, for the "Products:" field, if anything that doesn't exactly exactly match our list (the ones with the little squares) is going to get lost then we have to stick with typing fields. If anything that doesn't exactly exactly match our list lands in "Other" then that's perfect.

As I mentioned, the script that my dude made I asked him to modify it so that, for fields that are not predefined (exactly exactly), they get thrown to the RIGHT of the vertical line, with the notexactly field name as the header of the column, and the contents in the cells below.

In other words: there are three cases out of 500 where Grading Agency was instead written as "grading Agncy". The script automatically generates a NEW column (to the right of the vertical line), names that column the newbie new fields name (in this case misspelling), and then throws the contents (to the right of the : in the original Word/rtf file) in the appropriate row/record underneath that field/in that column. Kapish? So all you then have to do is to manually examine everything to the right of that vertical line and figure out what to do with it (move, create new field, or simply erase). If you want to move the contents into the field "Other", that is totally your choice. Each case will have to be manually dealt with at that time.

Of course, because I know how to optimise my time, once that ripfield (the field whose contents we will rip apart into many new baby fields) is copied back into Word, I can write up another macro which will s&r various misspellings and versions to make it exactlyexactly consistent. Once that is done, then we filter through the fancy script BACK into Excel, a fresh new file, again with the vertical line and all cases that I overlooked will have to be dealt with to the right of it.

I can imagine all this can be a bit overwhelming for you, but I guess you can see that my head is totally into this stuff and I can visualise it all at the getgo. And more things stumble to me as I'm working. The amount of years I've spent in front of the computer doing all that I do, I think my brain must work in binary by now or something. Anyway, just try to grasp what I'm saying, slowly we'll get on the same page, and you'll have a much richer understanding how these database things work, or need to work. At the end of the day, consider how meticulous and logically thinking I am, I can guarantee you that your final product will not at all resemble the slop you've described to me in the past, concerning the previous database work. It's just a matter of bringing you up to par so that you have a reasonably good overview, enough to help you decide the eternal question, to clicky or not to clicky.

For Species: for example, that I can edit inconsistencies (capitals, hyphens, spelling, whatever) to make it perfect. But products is absolutely going to have words not on the list. There's such a wide variety it would not be possible to make an all-encompassing list.

Hence my request, in anticipation of all this, to the programmer to modify the script to take into consideration all non-predefined field names (those that are not ee - exactlyexactly).

Another option is, once we have quickly and manually gone through the newbies to the right of the vertical line, extracting certain cases, the REST can be easily merged into the Other field, instead of having to individually copy/paste each cell etc. With a merger all columns get merged into one.

I will log on to your treeplanting thing tomorrow, I suspect I am using the wrong words for a couple of things which is making you confused.

I gather you meant translating and treeplanting. I jumble the two often.

And having already admitted that my brain thinks in binary like a computer, I get confused when your terminology is not exact, cause it does not fit into the perfect logic of things. So perhaps it's a gap in your knowledge, that you do not realise two things are very different, when in fact you are jumbling them together, and when you do that in computer land, especially on a pc, you get the horrid blue screen of death and then you have to press the restart button. Maybe if I had some gonja we'd be on the same page sooner than later. My head is way too sharp now.

Hope you enjoyed your sloppy garlic artichoke yesterday! I had boring tuna salad and soup today, boohoo (no time to cook).

Had it the next day as well, which I guess is technically yesterday now. Hope to have it for the next week. Without my beloved beer in my diet, I can afford to eat these fancier foods and still stay within my budget. Am gonna try the bowel cleanse too this time, and drinking juices for a while after the fast. Getting better every year!