InfoPath Dev

I am having a very hard time figuring out how to use DBXL to push my form information into my database structure.  We are basically using InfoPath as a quick and easy entry point solution for a way to digitally track and find our adoption records.  (If need be, the entry portion can be wrapped into the maintenance app that is being written.. we just wanted to get them up and running ASAP as their fileroom is overflowing.)  The problem that I'm facing is that not all of my entities have a 1:1 or 1:many sort of relationship so the child scenario is not working out so great.  I have some that are a many:many relationship and these use cross reference tables.  I cannot for the life of me figure out how to define the cross reference table relationship with DBXL. 

For example, an adoptive family could be tied to multiple birth moms.  Any of those birth moms could have multiple kids that she's given up for adoption to several different adoptive families.  And even though there's only one biological father, I need to be able to track multiple "potential" birth fathers in the system as the birth mother is not always certain who the father is and can give multiple names.  Though it's likely rare in the system, it is possible that multiple birth moms could list the same potential father so birth fathers could have a multiple birth mothers as well.

Now, they are only entering in one file at a time, so there's no need to try and track this spaghetti of relationships in the form itself (I'm working on an entity based solution to maintain records and define these relationships as they run across them), but I do need to be able to get a "placeholder" of sorts in the cross reference tables so that I can link them for this quick and dirty data entry InfoPath form to be viable.  Is this just too much to try and force InfoPath/DBXL to do?  Should I just do it all in the silverlight solution?  Our hope was to role out the entry tool so they could start scanning in files and inputing clients while we're working on the more involved maintenance and searching app on the back end.  Here's my database diagram so you can see what I'm trying to do:

Hi Melissa,

Before I swing the bat, I have few questions:

1. Does this database have existing data in it? Are there other processes that update the database?
2. Do you have paper process today for gathering information? Hoe many distinct forms do you have? What do they look like?
3. What kind of reporting do you need generate? What are metrics?

In general, you can map one InfoPath form to one or more doctypes and each doctype can map to multiple tables but best practice is to have one form map to one doctype and that doctype has a single row in a top table that it maps too even though it may subsequently map data to child and grandchild tables. I have a blog post on working with existing databases and Hilary has one too. Let me know if you can't find these.

More soon,

Thanks Patrick :)

1. No. There is no current process for digitizing this information.  I'm wide open to any and all suggestions on how to best organize this.
2. Just 75+ years of records in a fileroom.  There are some Word docs and Excel spreadsheets that quasi-work, but stuff gets lost and takes hours to days to track down.  Especially because in the past it has been organized by date rather than alphabetically.  So if an adoption from 1980 got stuck in the 1950s by accident... it takes a while to find it.  No real forms to speak of at present.  The InfoPath form is not to replace a paper process, it is merely to grab the metadata we want to search by (and shove it into the database via DBXL) and to generate a barcode for each of the section coversheets so that we can scan in the contents of the file and present it via PDF when they find the client they're interested in via searching.  The paper players currently involved are:
   * Adoption client file (6 sections)
   * Pregnancy client file (4 sections)
   * Correspondance log in VERY long Word doc
3. Reporting wise we mostly need to be able to do searches on any of the involved parties and have meaningful links between records if they exist.  So if someone comes in and says that they were adopted 30 years ago and they want to find their birth parents and any other siblings they might have, we'd need to be able to locate all of those adoption files.  There will also be a piece that tracks correspondance so that in open adoptions the birth parents can send Christmas cards and what not to their child and the adoptive parents can send pictures and stuff to the birth parents.  A log entry is made (the aforementioned Word document) when this happens, but due to the difficulty in finding the files sometimes it can take months before they find it and track down the address to forward the items along to.

I'm going to go searching for the blogs in the morning, but wanted to do my best to answer your questions before I ran away for the day.  Thanks for all of your help!

Hi again,

Thanks for the quick reply. It should be very easy to set this up based on the meta-data. Once you have a form with the metadata on it you can generate a bar code and a print view and print those bar codes to stick on the paper forms (as a reference check and for inventory location management).

DBXL comes with search features. Once the XML data is submitted, you can search on the records using any of the metadata that was keyed in. Then, you can just open the form to get the barcode and location for the document.

Since quick search is your main goal, you probably don't even need to shred the data to a database. That will save you time. Later, if you decide you want to report on the meta-data, you can add a mapping to a DB then and reshred all the documents in a bulk operation. You can also add other forms that interact with the meta-data form later too, but probably no need to do that now.

If that doesn't make sense, send me email and I'd be happy to do a quick phone call and live meeting with you to show an example of a similar system.

Cheers,