InfoPath Dev

I just discovered I had Microsoft Infopath a few days ago and realized it may
work perfect for this project I am putting together. The questions I am
asking I did search for but didn't find anything. Besides, this is something
I need done quickly and if any actually knows the answers while I keep
searching, that would be great.

Thanks so much ahead of time! If I get the right answers like I hope, I'll
be sure to come back when I get started on the project Wink

MY SCENARIO:
I'm trying to design a form that connects to a database of information.
This information through use of queries and such will need to be gathered
through a form (Infopath I assume) and queried out some more. I'll then need
to be able to manually edit any of the information and then when I'm all
done, save a "snapshot" of what I just did and print it. NOTE: When I say
edit, I do NOT mean edit the main information that was derived from MS
Access, I simply mean that particular form.

You see, we're trying to basically create a "Machine layout plan" that will
have a customer information, the item they want, the machine number and then
it will have a list of the different "parts" that we must create. These
parts have other parts in them (a belt, certain type of bolts, etc). This is
all fine and good and normally for this we would not need to make changes,
however we do have special orders come in all the time that customers want to
tweak the stuff from what our "standard" is.

SO, basically we need to be able to grab the information from a database by
using a form to determine what from the database we need, then take a
"snapshot" of that information, and then allow additions, deletions, and
updates to the current lists of information and finally, have the capability
of saving that item in a folder (preferably, though if there are better
ideas, that works too), and then printing it off and then having the
oppurtunity to go back in, print other copies off or make more changes and
save again.

BASIC QUESTIONS:
I am pretty sure Infopath is the cheapest and most likely candidate for the
job so long as the use of Access is involved, my problem are these.

1.) Will infopath be able to take a snapshot of the database information
(the queries and stuff I used to grab this unique set of information) and
save a different copy IN infopath, allow changes to be made locally to that
file? (Or maybe just some other way, I dont' know.. but our orders must each
be unique due to the high volume of special changes we get).

2.) Can infopath have a table in it full of information (such as parts) that
will be able to easily add new parts, delete old parts or change current
parts information?

3.) On top of question 2, I was thinking on creating two tables... one
called "systems", another called "systemItems" and one more called
"systemBOM" the one called "systems" would be have the titles of the
different systems we have to build for that particular machine, then drop
down another list that would be the different systemItems (like a serial
number) that make up the different systems, allowing me to choose ONE
systemItem. Finally, we would have "systemBOM" which would be our materials
list. The materials list would be connected to the systemItem and after we
had chosen the systemItem number for each system, the materials list would be
displayed underneath each system. So my question is, can this be made
possible by use of infopath and access? Also, (more of an Access question),
would it be possible to connect more than one systemItem to a system
material? (I'm assuming it would be called a many - many relationship, but we
still need to recognize that the systemItem takes precedence).

4.) Based on what you heard, do you even agree that InfoPath & Access are
the right to programs to use for this or do you have something else in mind
that I haven't thought of?


I deeply appreciate any help I get!!!
--Mike

Hi Mike and welcome to our forum!
You'll want to install the Developer Edition of our Qdabra Database Accelerator on your server. It's free. Here's the link:http://www.qdabra.com/proddetail.asp?prod=QDBXL1 (see FREE DEVELOPER DOWNLOAD). This will be the fastest way to do this. This tool will let you easily pull data in from a database using the EnumDB Web method (which supports field-based filtering if you have lots of data but don't want to pull it all down) and then it enables saving the snapshot of the form in the database. We'd be more than happy to help you through this via a Live Meeting. It takes between 30-60 minutes to set up with an existing form and then you are basically done with the initial implementation. It requires SQL.

Here are some answers:
1) Yes, InfoPath supports this so long as the information comes from the main data source. Secondary data sources can also be cached. With out database accelerator you can save a snapshot to the DB and edit offline.
2) Yes, InfoPath can save a table too so long as it is in the main data source.
3) You can do all of that, but I don't recommend Access.
4) InfoPath and SQL with our Database Accelerator Web service. This is the fastest way to solve exactly the problem you have without writing any code.

I hope this helps.

This is absolutely wonderful! Sadly I do not have an existing form... we had someone about 10 years ago attempt to build something via MS Word and that is what we have been using.. I came onto the scene and they now want something better.

I would LOVE! Live help... that's my favorite kind because then you aren't waiting for answers and you can tell your boss that you're working on it ;-)

I don't care how we get this done, so long as the requirements I set up above are carried out... and so what if we don't use access.. but I will need to make sure somebody will be able to "edit" the database information (only a few people will have this right while the rest will use the main form to create their "sheets")

I will get more onto this MAYBE tonight, but more likely early next week. I'll be able to sleep better this weekend, however, knowing something can finally be done about this.

Again, thanks a lot for the help, I couldn't ask for better,
--Mike Reeves

I was wondering, how could I set up a live meeting to get this figured out. And when you mean live meeting... -how- do you mean?

Anyway, yea I'm fairly interested in getting something taken care of.

Thanks a lot!
--Mike

Hi Mike,
Just send me e-mail. You can click on the "E-mail Poster" icon which is second from the left in the icon list at the top of each post. Keep in mind that while we will help you set it up, you will have to finish your InfoPath form yourself. I can give you some pointers here, but it's best to create the InfoPath form first paying close attention the data source. After that, we can quickly hook it up to the database. Also, while the Developer Version is utterly free, it only supports 100 forms (and 5 document types, or form templates), so in the interest of full disclosure, you may have to purchase a license after you start using it. I think that you and your boss will see - like many other companies have - that it's worth much more than the cost. If you haven't clicked on the link above, it contains more informaiton.

Anyway, send me e-mail and we'll set up a time next week. I look forward to talking with you.

I went ahead and sent you that email. Thanks for the help!!

Alright, I'll explain this as best as I can. I finally decided how I want my form to look.

I have:
----------------------------------------------------------------
Machine Type: Machine Name:
DROP DOWN -- DROP DOWN (Affected by Machine Type)


BOM:

SubMachineTypeName #xxx-xxx-x (Where x = model number of this particular machine)
(This part lists out all the information such as Part# for the specific part and the Description, etc...)

SubMachineTypeDiffName #xxxx-xxx (Where x = model number of this particular machine)
(This part lists out all the information such as Part# for the specific part and the Description, etc...)
--------------------------------------------------------------

The key is, our major machines (The part where you select from a drop down) will have some SubMachines and not others that maybe a different machine has. HOWEVER, some SubMachines may be on a certain Major machine, but not the same as say, the first one we built. That's where the part number after the SubMachineType comes in. So SubMachineType could be, Aggregate Bin or Cement Bin or any other name (obviously only one per Major Machine), and then after it, I want to have it set up so i type in the SubMachineType's part number and it'll list all the specific parts underneath that.

So here's a plan of how I want the creator of this "sheet" to do it.
(Where the different fields are in quotations)

1.) Drop Down & Choose "Machine Type" which will affect step 2
2.) Drop Down from a queried list in "Machine Name"
3.) When that occurs, it determines which SubMachineType's there are and then what our DEFAULT part number we have assigned to each SubMachineType FOR THAT PARTICULAR MAJOR MACHINE.
4.) The creator of the sheet would be allowed to type in a different part number for a "SubMachineType" and have it repopulate the list with what is assigned to that Part Number
5.) The creator must be allowed to physically delete certain parts or make changes WITHOUT AFFECTING THE DATABASE (This will ultimately 'create an instance'). This will only affect this particular BOM for this particular machine.
6.) The Creator must be allowed to save changes, print it out and come back to make changes and print again should he/she need to.


Now here is how I need, say the "Owner" or "Administrator" to be able to handle info in the database:
1.) Allow the addition/subtraction/editting of specific parts (NOTE: the parts that are inside the BOM, such as a part for the Aggregate Bin should ALL BE PLACED inside of one 'table' in the database. In this table, these parts need to be able to be assigned to more than one SubMachineType xxx-xxxx (Say we have a large bolt that works in both the Aggregate Bin 1234-033 and it will also work in Cement Bin 333-442322).
2.) Allow the addition/subtraction/editting of SubMachineTypes, where they are assigned and what they BOM is.
3.) Allow the addition/subtraction/editting of an entire Machine, preferably from start to finish.

----------------
At the moment I only need to worry about the "Creator" part and getting the entire structure in order. I can manually go into the database for the moment and add stuff.

Just to let you know what I have:
MS InfoPath 2003
SQL Express Server 2005
Qdabra Download Accelerator
Qdabra Download Accelerator's "Web Form" for a SharePoint website.


Otherwise, I have no idea where to even BEGIN. I know it's a lot but maybe if I could get some pointers on how to start this. I can use MS Access fairly well and can do queries fairly well there, never used SQL but I'm sure it's not much harder. My only major problem is not only do I not know how to effectively use InfoPath (don't worry, going to get a book, but REALLY need to get this, pretty or not, done soon), and SQL together, but also the idea that I have no idea what to do about how to get SubMachineType and the Parts to play together.

I'm pretty sure my structure needs to be influenced in this order...
1.) Machine Type
2.) Machine Name (Queried by the "Machine Type's" ID)
3.) SubMachineType (Queried by the "Machine Name's" ID)
4.) SubMachineType's Part# (Use SubMachineType's list and find the right part# based on what the MachineName wants)
5.) Parts (Queried by the "SubMachineType's" ID)

Two problems arise from this however, that I don't know how to deal with.
First of all: Between 2, 3, & 4 I don't know how I need to go about setting that all up.
Secondly: I don't know what I need to do to allow for Parts & SubMachineType's Part# (5 and 4 respectively), to do a many to many relationship.

I don't know... I'm sure much of this seems very VERY elementary, and I'm sorry that it is so. I need to build something not only In-House, but on my own due to budget restraints and I need to build something soon... Absolutely no later than a month from now to get something absolutely working perfect. I've got to buy a book I know, but it would be HUGELY appreciated if people could start pointing me in the right direction here.

Any questions with what I'm attempting to do? I would be absolutely more than willing to answer them!

Thank you TONS,
--Mike

Hi Mike,

Well, that is quite a post! Hopefully we should be able to get you going in the right direction. I'll start with the "Creator" form (as opposed to the Administrator form) as I think it will be easier.

1. Create a new database with all of the appropriate tables, from what you've mentioned it sounds like it will have these:
-- Machine Type (parent)
-- Machine Name (child of Machine Type)
-- SubMachine Type (child of Machine Name) (contains part number column)

2. Create the form
-- Add 3 data connections to QdabraWebService/DatabaseEnumeration.asmx, one for each of the tables in the database created above. For now, don't worry about dynamic filtering and pull all of the data down every time. This could be a big performance hit later, but let's get things working first.
-- Create cascading drop-downs for Machine Type, Machine Name, and SubMachine Type, use these tutorial for help:http://infopathdev.com/howto/tutorials/default.aspx?s=Filters
-- To allow users to create "one-offs" of new part numbers, you could add an "Other" node to that drop-down and then hide the drop-down and show a text field. To do this, you can add a "dummy row" to the database data connection that gives an Other option, or you can simply add the Other row into the actual database.http://infopathdev.com/howto/tutorials/default.aspx?i=7f693c75c5c94604b01bdfd3a1edaedc
-- All the save, print, open, edit, and resave commands come native with InfoPath. After opening the form template and filling it out, users will save off an XML file that can be opened later and edited and/or printed.


The only thing you will need DBXL (our acroynm for Database Accelerator) for is the Admin/Owner form. For this form you'll want to setup a schema similar to that of the database and then create a mapping so that the form can create new data in the database. The thing here is that once you create this form for the mapping you will need to start from a clean database as DBXL cannot handle rows that it did not create. The admin form seems like it will be a little less complicated than the other form, but the other shouldn't be too bad.

I hope this is enough to get you started. If you still haven't gotten a book I would take a look at all the tutorials we have written on our website - they are really a great resource and explain a lot of cool tips and tricks.

Wow... really stupid. First, this may help.. does anyone know a good place for newbies to read up on SQL Server Express 2005 (some kind of online tutorial to get me on the right direction)?

Until then... How can I make a connection to the machineType and machineName? I look under relationships but I don't know where to go from there. Also, should I only create three tables, or four?

I thought I may have:
1.) machineType
2.) machineName
3.) subMachine
4.) subMachineParts

Or is there a better way with just three tables?

Anyway, I'll be around all day trying to figure this stuff out. Thanks tons for all the help!
--Mike

Umm, couldn't find an edit button (though I think I just realized I wasn't logged in...

Anyway, here's how I truly think my structure is (ignore the last post).

Tables:
1.) machineType (Ultimate list of the types of machines we have)

2.) machineName (the Model Numbers of our machines, queried to only show the machine type that was asked)

3.) submachType (The type of submachines we own. These will be queried out based on what the machineName wants. NOTE: There will be more than one submachine type for each machineName and ALSO we could have the same submachine type be for multiple machineNames!)

4.) submachName (identical to submachType as machineName is to machineType. This will be a list of (we call it a part number, but you could essentially call it a model number of this sub machine) that will be queried out based on the submachType we have. NOTE: There will be multiple submachName's for a submachType, but there will NOT be multiple submachType's for a single submachName... this holds true for machineName).

5.) parts (An "inventory" of every single part we own... these are in the thousands upon thousands. When someone will punch in the part number or look at the drop down, this table needs to be queried to create a BOM for that submachine Type.)

NOTE: This particular message deals ONLY with the SQL portion of my problem. Once this has been effectively figured out, I will reiterate what I am having problems with on MS Infopath. I know, this is an infopath forum but please do not flame me or anything regarding this. You guys have been the only ones willing to help me with this in a quick fashion and I don't want to have to explain something that feels kind of complex to me to multiple people. Please refer me to a good SQL Server forum should you guys frown upon this. But ultimately, this does relate to Infopath, so hopefully you understand.

Thank you!
--Mike