Unanswered: Automatic import/append of spreadsheet used for bidding on projects?
Ok... i have scoured the Code Dump, and I don't think there is anything in there that can help me. I am working on a database for a company that does bidding on construction/rehab type projects in the housing industry. What they would like is an excel spreadsheet that can be filled out in the field and then through some "magical process" (which i have not figured out yet), sends that file to the Mother Ship database and the database appends all the data.
Here is my question....
I know i can save import steps... i know i can have named ranges in the excel spreadsheet...
how can i specify that cell A5 = new record's Property Owner, A6 is the property owner's email address etc. ?
Also, the tables i have are:
PropertyData (contains things like address, number of bedrooms/bathrooms/etc.) which has an autonumber for its unique bid number.
PropertyBidSpecs (contains data about what is being bid on and the quantity). It is linked to the Property data by the unique bid number that is auto populated in PropertyData.
How can i "grab" the autonumber for the new bid property and make sure that when it appends, it takes that number?
I guess I would use Excel automation to read the Excel file and then pull out what I need. A big problem might be if the people in the field "adjust" the file and then things aren't in the places that you would expect. You'll probably have to lock down the Excel file so that they can't change any formatting etc. Do some searches to get started...Excel automation isn't that bad.
I'm not sure I quite get the next question in your post. If you are trying to figure out how to get the autonumber from PropertyData to use in PropertyBidSpecs then I wouldn't use autonumber. Generate your own bid number that way you know what it is when you want to add something to PropertyBidSpecs.
hmmmmmmmm... how would you suggest i generate my own bid number? I know that sounds like a dumb question... but hey... i am feeling a little dumb right now. I will do a search for Excel Automation... any particular site you would suggest? Thanks
Look in the Code Bank for information on Excel Automation. As for the bid number generation...there are many different ways to achieve that. You could use an autonumber field along with a timestamp for when the record was added. Then after you insert the record into PropertyData you could look for the Max of the timestamp (assuming only one person is updating the database at a time) to get the bid number. You could generate the bid number yourself based on some criteria you come up with.