Form Fields Translation Conversion Data Search

okay, just researched this and the records are the same as the offline Access, so the error would be somewhere during the import process. Off to analyse that. Important to work out these chinks first, cause the import process is time consuming, and a small but fundamental glitch might mean I'd have to reimport everything again, and then convert to online etc., which can get pretty time consuming.

Turns out you didn't forward one of my emails to the techhead, or he didn't respond, but the script aint workin now that I've tried to put your dbase out of the html folder, so I will halt with development and focus today on conversion (including the clicky boxes). Need to get the conversion right before proceeding. I suggest you analyse the Access data for any glitches, since all the data is visible there, unlike what you see online.

yes, it was the website field but it wasn't skipping every single entry, in the middle it was correct. I thought maybe there was an extra comma/tab or a comma/tab missing?  website field, the first entry is correct, for the second entry (Boucher) the website for the next entry (Buchanan) appears, same with the next time (Clareco's website is under Canfor, but the email is correct). I guess this means an extra comma/tab was missed somewhere? then when it gets to Foothills the website is correct again but at LaCrete it is the email that is off by one but the website is correct. hmmm, puzzling.

> > something occurred to me yesterday that I forgot to write to you

> about. You were

> > saying something about email addresses misaligning or something?

> This is very

> > important. Before I proceed to import more data from you rtf

> files, I think it

> > would be a good idea if you went through the records I produced

> and confirm if

> > things lined up properly? Or what misalignment were you talking

> about?

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

okay, took a look at the two forms you sent me and they seem rather different. Any more forms you can send me? It seems that the best approach, at least for the two scenarios you sent me (New Mills, New Wholesaler) is to make a separate form for each. In that case the Category Type should be a select box, with only limited choises, such as Mill, Wholesaler etc (perhaps send me a full list to give me an idea about structuring). So when you view a record and click Edit, the php will look in the Category field, if it sees "Mills", then it will open up the Mill App form. Get it? So before I started putting together a form we need to come to some agreement on this. Not sure how I'll handle this for Access and may have to brainstorm/research a bit before I commence, so that I don't waste any time later.

For the two forms you sent me, I assume and believe you said Business Type is not important.

The Species Handled seems pretty well the same for both.

Do you want a drop-down menu for any of the items? For example, I gather it would be silly to have a drop-down menu of 1 to 99% for the Species Handled section but rather you just punch in the exact percentage. Especially if the percentage could also include some decimal points. But how about Rough and Surface Sizes for the Mills? Is there some prescribed list for each, or you wanna punch in those figures as well?

If you want a limited dropdown list to choose from for any of the fields, now is the time to mention it.

Would two forms be enough, or more for some of the other categories. Not a problem to create a form for one case, then Save As, and tweak it for another case.

I have just seen a potential problem with the clicky boxes. For the conversion process the text has to be EXACT. For example:

Mill (category in a separate section)_Products_Studs_2x4-8ft = YES

would get converted from (for example)

2x4 8foot

in that field before we convert it.

But if sometimes you have "2x 4 and 8ft" while other times you have "2x4, 8 foot", you can only imagine how much extra work and cost the conversion is going to require. Computers are not intuitive and say, "Oh, I guess she means...". They are extremely stupid in that sense and everything has to be bank on exact.

Opening up BC Mills just for an example, I find:

(2x4 to 2x12, 8ft to 16ft), boards (1x4 to 1x12,8ft to 16ft), timbers (up to 10x10, 8ft’ to 16ft), tongue & groove, channel siding

Right away this sends off an alarm signal because it tells me that some intuitive person (certainly not a computer) is going to have to expand your little "to", or "up to". Meaning fill in the gaps, and an innocent looking section like Timbers (up to 10x10) will need to first be converted to timbers_1x10, timbers_2x10, timbers_2x2... Shit, I look at the app form and I do not even see a section for this. Perhaps my Siberian dude can make a fancy script, or I some Macro, but if little extra characters are thrown into this goulash, like an extra space, or quotation mark or whatever, it's just going to screw up any conversion script and only require more manual conversion work.

A special field will need to be created for each case, like timbers_2x2, AHEAD of time, so something for you to consider before we proceed. You conversion budget will certainly not cover any of this work.

I guess we can analyse the situation once it's all imported into Access. There it will all be in one field, and I would have to convert it to that format first before I would break it up anyway. If you decide its going to be a major amount of work, I can proceed with the form, perhaps start with clickies only for certain fields (such as Lumber Products Manufactured), and then take on the bigger headache sometime in the future. But again, if we start with Lumber Products Manufactured, you cannot have "Funiture Stock" in one field and then "Frnitur Sto ck" in another. If there are discrepances like this, it will all have to go towards an extra "conversion budget". Perhaps a long term project that you are I can piddle with while the database is already alive and active. Yes, there will be some extra work required in changing the webforms and Access to display the field info once they are broken up into a billion new fields, but that seems like a paultry cost compared to the work required for the conversion process (breaking up into billions of fields from exactly and correctly named text, no spelling mistakes etc. allowed.

Same applies for Shipping, if you want that in clicky boxes.

Okay, perused both forms and that's all I can think of for now. I wont start on the form until many of these issues are answered. If you want me to give you a conversion estimate price, perhaps let's wait until everything is in Access.

latero!

Ok here's the Wholesaler form, with the other list of products. Some of them overlap with the Mill form I already sent you. (not zipped because it's tiny). I now realize that there is still going to have to be a large field, like a notes size field (more than 255 characters) even when you do the click boxes. Is that a problem? Both the Panel mill and Pulp & Paper mill forms are basically a choice of two or four boxes then a whole bunch of lines to fill in. As you said in an earlier email, how often does this get updated? Not that much. Its just a matter of filling in everything for a new mill, but not that many new mills open in a given year. So . . . I would like the boxes but can see how its kind of a pain-in-the-ass amount of work for not that much benefit so if you suggest not to do it I will go with that.

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

okay, doing a bit of brainstormin here. Better to do more brainstorming now that endless repiddlin later.

First of all, are the clicky boxes thing mostly for EDITS or new additions? If new additions then I would suggest the following:

write down EVERY possible bobblyboo, and I write it into a fancy form. Perhaps a single one. Still have to think about things.

For the clickies, you get various options. There can be a dropdown list where you can make MULTIPLE choices, by holding down either the ctrl or shift while pressing with the mouse. This is fairly easy. In this way you can select your ranges etc. Once all choices have been made, when you press Submit they all get MERGED into a single cell, written as you want it [Timber (1x2, 1x3)...] I doubt your going to want to display them as individual fields but rather some longer text field, in which case we've saved ourselves a lot of work, since I wont have to create an output field which merges together separate fields in the database.

Now this brings to question the possibility of using a new technology I have been learning: AJAX. I have to learn it anyway for one project I'm working on, but with Ajax, the appform (new additions) actually become dynamic so, if let's say one field is dependent on another, you would make a selection in the other, and then the next field would readjust. For example, you have a country field and you choose the US, in which case the next field lists the states of the US. You then select a State, and then the third field pops up with the list of cities within it. Without this system, it would be ridiculous to have a dropdown list with all the cities in the world - just to give you an example how the system works. So do you envision a need for something like this, or would a single large form suffice? It may be a longer form, but probably wouldn't take so long to load. YOu can have a metric section and just skip over the imperial section. Or what if a company provides both metric and imperial? In which case all in one form would be better. Let's have a totally clear vision of this before I proceed.

Alternative 2: you would like to use the form a lot for editing data. Could be a lot of work, and I'll brainstorm about it if that's what you want. But lets say a company expands its selection from Timber (1x2 to 1x10) to Timber (1x2 to 1x12)? All you would technically have to do to edit is to change the 0 to a 2, and you're done. Sure, you'd have to stare at the large text field and burn your intelligence a bit deciding where exactly to make the change, but perhaps it would be relatively painless.

If we go Alternative 2, to display the results, I may be able to make some fancy form which shows a backwards Appform, with the ranges showing as selected, or not showing the unselected ones. I'd definitely have to think about this but all should be possible. Can make it graphically appealing, instead of just a big blog of potentially cryptic text. I'd suggest we bounce around some ideas before commencing on this.

On that note, what do I do now? I guess just wait for you and work on my own project, heh heh. Well, at least I can get the online thing set up in the new folders I've chosen.

Think I've covered most of this email but I'll save it and get back to it again later. Gotta do some Slovakian translations now. later

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

you know what the form is I gather by now, well the datasheet is the view when you see the records from all or some of the mills at the same time, with some chosen fields to view. It just occurred to me that to copy the data from forms the person would have to copy/paste each field separately, for a single mill at a time. Which would get even worse if you decide on the clicky boxes. Even without the clickies, considering the number of fields and mills/records, the person would be spending absolutely forever to steal your database. The trick I guess is for you to quickly make yours a known standard in the industry, so that any competition would have a hard time gaining your reputation etc.

In any case, considering this point, we can slowly start talking about what fields you want visible in the datasheet. One possibility is to have a search form where the user would input "show me all mills which produce a 1x6 or greater wooden planks", but the search results would not SHOW the wooden planks field. They would just have to believe that all the results show are refined for that particular search. Then they could press on the Details for a particular mill and see all the data for that mill at once. So I would suggest you reflect on this so that we can plan a bit for the datasheet view. It really should offer more interesting details then just the absolute basics. Or at least it should have a few Search boxes, where the person could fill in criteria for several fields, such as "1x6 or greater planks" AND "mill capacity less than X per year", if you know what I mean. You don't have to show the results of the search in datasheet view, but I think it will make your database look so much more kickass and powerful, if people can make sure refined searches.

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

sending this from the PC, zipped files of listing forms. The first one is the various mill types (not including Primary and Wholesaler, which you already have). There was a couple of sections which I deleted, the Reman form uses another word for Services, just keep that as you have it. So the only thing you have to add, which you probably already did, is Distribution Yards, from the Wholesaler form. As for the clickies / pull downs, I think we should just do it for the things that are obvious: Species, Products (including the range of sizes, so where it says 2x4 then 8ft, 9ft, 10ft, 12ft, . .. etc. again with Studs, 2x4 then PET, 8ft, 9ft, . .. etc.), note that the Shake & Shingle form has it's whole own list of products (again do it as it appears, with the item and the size so Shakes then 5/4 blah blah blah). The Panel form just has the four types of panel (of which almost everyone only does OSB and Plywood) then spaces to write sizes, lets leave that as text fields. So potential for one mill to do all four types of panel, which doesn't often happen. Same with the Pulp form, the various types of pulp then just put text fields for the quantities, chemicals, . . whatever. I mean it can all be text fields, just be sure here is room to fill out all four types of pulp, in case something weird happens and there's a mill that actually does them all. As a super bonus added surprise, since you asked for all possible eventualities, the second zipped file is things in the printed book that I could potentially expand into. Truckers and Marine transporters are good future sources of advertisers. The Reload Yards in particular is extremely valuable and useful information. I should almost include it immediately but that's a whole few more weeks to research and update and I don't want to delay right now.

Well things can be added later. Technically you already have a functioning database up on the web - only about a hundred records, a few hyperlinks and emails are misaligned, and the details part is not available. So it is not a problem to expand later by adding fields etc. Not a major surgical operation. But what I think would be beneficial if you at least gave me an idea WHAT you might want to add later, cause it might give me an idea concerning certain core restructuring which would save a lot later. Would be cool to expand into other areas, cause it would give the dbase more exposure and hopefully me more future business.

The Rough and Surfaces Sizes you asked about, for me to write out all possibilities, well I'm a bit reluctant to do that because all that needs to happen is some new product/new size to start being made, and - like I said before if I go global - Europe and Asia are in metric which starts to get ridiculous. So for those sizes I am leaning towards just filling it all in manually.

Again, a rough idea of what might all be added in the future would help me now during the structuring stage. Then adding fields here and there, or a special form for a different category, isn't really a big deal. Also, if some company or whatever might be in two categories. Could that be possible? Worst case scenario you just fill in that company using two forms, or I create a third form which would take snippets of the other two forms. The forms are simply windows to different fields in the database, so you can get into the house from different windows - the house is still there.

Today it's Sunday and I'll give myself a nice little break. Last Sunday I had to work so basically I've been working nonstop for two weeks. It's sunny so hopefully it will stay that way and today I'll bring my camera, finally, to document the border of the city. Every Sunday I've been going over to the Turkish side to shoot some pool, and meandering along the border along the way. Last divided capital/city in the world - certainly will want to document it. Have a good Sunday!

It's 3:30 and I haven't even eaten yet so I probably won't do any more on this today. Tomorrow I will finish the research part and send the BC Reman, Wood Pres, Whole which I have done but not sent and the Canadian Reman, Wood Pres, Whole. You will have everything at that time and can start moving forward with all this stuff that you've been waiting to see.

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

There won't be any monumental changes or additions if that's what you're asking, just more of the same. The scope of the sawmill listings will be by far the most detailed, whatever I do add later (grading agencies, transporters, international companies) would fit within the Name, Address, Phone, Fax, Email, Website and possibly Services fields.

again, it wouldn't be a big problem to add the occasional field here or there. The real challenge will be the clicky boxes and conversion from rtf of those troublesome fields. Will need to get a good grasp of this before proceeding.

there are companies that are listed in multiple sections, for sure. I mentioned that before. Could be three: Mill, Reman, Wholesaler is the most usual. Sometimes the listings are exactly the same, sometimes they differ slightly (people fill out different products for the Reman section for example) sometimes they have two facilities under one address which do different things. The Reman is fed from the Sawmill. At this point you do have actual multiple listings: if the company has a primary Mill, and a Reman, there is a list under the Mill section for that province, as well as the Reman section. They can't be melded into one, they must remain stand alone, and yes I have to go through the pain in the ass of updating both if one changes, that's why the "Notes: see also listing in BC Wholesalers section" part is extremely important.

Will be good to discuss this further. For example, if one company lists itself in two different lists/forms, why separated it at all?

We could consider the following scenario.

Company X manufactures A, B, D and F size planks, with all sorts of details like contact info and address etc., and is in the Manufacturer category. But because of the synergetic beneficial effects of vertical integration, it is ALSO a wholesales, selling A, B, D and F sized planks. And all the other info is the same, except it has additional info for the Wholesaler category. In that case you can put them under the same record.

BUT, if, as a wholesaler, they ALSO sell E and G sized planks, then you would have to create 2 records for that company, X-manufacturer, and X-wholesaler.

If you do NOT need to separate them, when the user selects company X, category Manufacturer, they will see their details through the special Manufacturer window/form, showing only those fields relating to manufacturing and drawn from the SAME record. If they want to see X as a Wholesaler, they view them through the Wholesaler form, most of the fields drawn from the same records, but with some additional fields and data relating to the Whol form.

If this happens a lot, perhaps it would be good to create one very long input form which covers all possibilities. There could be a line across the page where it says "Wholesalers section starts here" etc. If you click on planks A in manufacturing section, I think I can make it so that the A plank clicky box automatically gets selected in the Wholesaler section. This way, with a merging of forms, you don't have to bounce around all over the place.

If however the data is most often different as I mentioned, you would need two separate records. But lets say half the companies have the same info while the other half have different info and require a different record to be created. EITHER WAY, you can STILL have one very long form. Something to think about. Need to be very clear on all this before I commence with the forms.

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

hmm, there's something wrong with that form. I resaved it as a different name in PageMaker and tried it again but Acrobat freaked out. It's not important, the only thing different on that form is "Preservatives Used:" which I am totally willing to sacrifice. If you look at the Pulp & Paper form you will see it has "Chip Storage Capacity:", I don't care about that either. I am willing to either lose it or have it bulked under "Services:" Losing these finicky things is fine, for these two types of mills. Hardly anyone ever fills it out, and when they do it's usually the same batch of stuff.

Either that or just write me instructions which fields to add or erase etc. What I'm thinking I'll do is, after reading the billions of emails and try to compile all the data, I'll make some html forms and send them to you. You can open them in your browser and give your comments concerning them before I proceed. Obviously they wont work or anything but the structure will be there.

> The listings forms are:

>

> Mill

> Shake and Shingle

> Panel

> Pulp & Paper

> Remanufacturer

> Wholesaler

> (wood preserver)

>

> the extra email with the other three forms of super fun, ever so

> much more boring but potential great source of revenue for me was:

>

> Lumber Transporter

> Marine Shipper

> Reload

Yah, lets expand. Shouldn't be much pain to expand the forms a bit into new areas, but the more eyes that see this the more customers I might get out of it. If we make an app form for new people, you wouldn't have any extra work with new dudes adding themselves to the base.

so if you want to tackle future work, something I will expand into next year or whenever, then that's it right there. as for other expansion, after that it will just be more of the same. Next logical thing would be US mills, the maybe US transporter. European mills. This would involve metric sizes. then Asian mills, also metric. The rest of the Directory is Grading Agencies (I will not list those but can put outlinks, I think I have them already on my site) and Consultants (forget it, if they want a listing they can pay for an ad). The other potential future thing would be biofuels, both gas and pellets. Noone knows how this is going to come about it's brand new, so far usually it is a partnership between a lumber producer and an energy company. so that's it!

ok!

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

> 1) Beaubois Coaticook Inc.

> what's the question? the weirdo freak name? get used to it!

Sometimes you had a couple of question marks at the end of the line, so I thought I'd put the < character there to remind you of it. I think there were like two or three cases of that - maybe this was won of them. Otherwise it should be fairly logical everytime I put a < in.

For Rob's Directory, they went and listed all the companies with Le, Les at the beginning under "L", haha, idiots! It used to be that way when Peter Madison first started, he didn't speak French at all and didn't know. It was a big joke in the industry for years.

Sounds like a mighty dork.

> 2) Cedres Basques

> YES that is indeed the name

> 3) Cobodex

> no, delete all "Representatives For:" we don't need it.

I'd appreciate it if you went through the files and made any changes, possibly add comments in [] brackets, but kept my < characters there. This way you're making things a lot slower and tedious for me, but if you already went through the file I can dig it all up. If you have any files left to do, do it like I suggested.

> 4) Domtar

> either the name Nabakatuk is the question, or the list of divisions,

> either way YES.

> 5) Kruger

> YES

> 6) Richard Pelletier

> YES, erase "Business Type:"

> 7) Thickness:

> oh it's a measurement for Panel, you will find it on the panel form,

> its pretty important. it only shows up for the panel mills though.

> Let's keep that.

> 8) Plywood Thickness:

> same as Production:, either one field for Thickness: but then it has

> to say plywood 5/8, 7/16, 15/16

> or there has to be separate fields

> Plywood Thickness:

> OSB Thickness:

> MDF Thickness:

> Veneer Thickness:

which seems kind of dumb because, unlike production, it is only for the panel mills and there aren't even that many mills that do MDF and few that do Veneer so it seems dumb to have a field that will only be used like 20 times overall. I was trying to avoid that. Can we munch it together for thickness, but capture which type of panel it is within the field? for production I don't know what to do, need your expert opinion.

That's why I sent you this and we should deal with this now. Obscure data fields can definitely be added/merged with other ones. Just like I did with the Contact Persons: simple added "Vice President - ..." to another field, rather than have a Vice President field on its own.

Anticipating problems, I got my programmer to alter the code (still need to test it) which would spit out the uncaught and obscure fields at the end of a row, at which point I could capture it and merge it into one of the other fields.

> 9) Plywood Production, answered above, awaiting your decision

> 10) plywood thickness, again, above

> . . .

> 14) oh god there's Waferboard too, bloody hell. the panel listing

> form will show you all the possiblities.

> 15) I don't even know how that 3/4 got all fancy like that, but

> yeah. all these fractions should be uniform, I'm sure.

Probably one of those often annoying features in Word which turn c) into a copyright symbol without even asking you. These will need to be found and unfancified, otherwise they could turn into something totally different during conversion.

Ok from what I can see you have a good handle on this. You have not seen Wholesalers, Remanufacturers and Wood Preservers yet, and I expect you might find some new finicky things we haven't addressed yet. Particularly with the Wood Preservers, because there is a field for Preservatives Used: It doesn't help that that form is somehow mangled, tomorrow I will try to open it in InDesign (its in a super old version of PageMaker) and see if I can resurrect it.

So you cant open it at all either? If you can, you can always just send a screen shot image.

I will for sure have this done tomorrow morning, I know I said I would have it today but the shit hit the fan in a big way Friday and the world is in an uproar. I am almost done, seriously shouldn't take more than an hour.

I keep getting more work, but I may have to put a stop to it for a while. Feeling like I'm clanging my head on the stupid wall again and need to switch to something more intelligent, or finally find the time to go to the gym and shower. Been almost three weeks I think, OMG!

Actually I am finished the Remanufacturers across Canada so can send you those first thing tomorrow if you want to see it. I think you asked something about "Why is it regional?" and also "Why keep the listings separate if there is more than one". there are good reasons for that, people search by geography, in the book I mean I don't suppose online it will matter, if they search QC they will get all Quebec so that's OK. but we can't combine

I think I suggested to definitely keep the regional aspect. So I guess we're on the same page. Good.

Mill/Reman/Whole into one. There are different reasons why people will want to know only primary mills (log suppliers for example), or only remans (equipment suppliers that make something special, like fingerjoint machines or whatever), or only wholesalers (customs brokers lets say) and they don't want every second entry that comes up to be a Mill or Reman. Basically, however it displays it does need to be differentiated, and show up in its appropriate section.

So you're saying to merge these categories? I'd definitely suggest against that. If anyone wants to see Mills and Reman at the same time, that is easily doable with a search box which would give a result something like "Show Mill AND Reman records". Like I said, not good to throw away data like this, since it is separated, and can be easily merged through the show search, but always separated in case someone interested in honing there search into a certain area, like:

Quebec AND Mill AND Sizes greater than 1x3

OR

BC AND Renam AND "Bob" in Mill Name

The "And" MUST be met, so it is like two searches, and in our human brain the OR would be like an AND, cause it would show both of the results, except the AND and OR are "logical" functions. Don't wanna explain now. Maybe/probably I'll make a website explaining it for future customers to help them understand how these searches work, and hence to help them how the database should be structured.

The Endless Clickies Question I think I addressed yesterday: clickies/pull downs for things that are manageable (Species, Products (with all the size), Shake and Shingle products (with all the sizes)) everything else can be typey fields. Thanks so much for being so diligent with this, I know one of the other companies I got a quote from would not be. they think they have seen it all already, take a quick scan down a sample page, assume they know and just blaze ahead. the guy that did the database for Rob I actually explained to him carefully that these problems would come up, he just kept saying, "Oh I have a script for that" "oh the software can handle that" then went off and worked for two weeks, delivered something that was grossly flawed, I sat on the phone with him while he fixed the worst of the emergencies, left him with a list of final things to do (by then my replacement was doing my job), I heard later that he totally messed it up, some fields came in empty, others were in the wrong place and she literally had to go through every single screen correcting stuff.

I can easily see this, and predict it. Just occurred to me that you could write up a nice testimonial explaining all this, once the job is done, which I will then post, cause many people might be surfing for price and they should be warned of all this. They might save something as opposed to me, but end up paying gjillions in other ways down the road, mopping up all sorts of mess. Especially if the programmer ploughs through and delivers, gets paid, and then runs away. Once the customer stumbles on things it can pay an equal amount getting a real professional to patch everything up.

Oh I guess I said I would have a third look at the Access file, couldn't do it today will tomorrow morning. Haven't looked at all online, I think you said you had something up?

I think I have it running now in the new folders (out of Keta), but nothing new to really check out. Online version is still rather boring and undeveloped.

> thanks again for the good work. I'm stinky and need a beer.

I'm in my fast and no more beer until Easter, boohoo.