8 Replies Latest reply on Feb 27, 2014 12:10 PM by SomeFileMakerNoob

    Importing and storing data

    SomeFileMakerNoob

      Title

      Importing and storing data

      Post

           Hey All,

           I'm pretty new to DB programming, loving FMP though. So far has been easy sailing. Finally hit a brick wall though. Basically, here's what I'm looking at. We have a customer DB where we import a monthly Excel which contains about 60k+ lines every month. What I need advice on is...I don't know how to explain it, so bear with me please. Here's what I need it to do.

           1. Import new month and display in main search layouts

           2. Previous months data is stored for 6 months and is viewable in a separate layout. 

           So, basically I would need the newest data to be displayed in any newer searches, then I would have a "history" portal for each of the previous 6 months. I'm sure I'm being very obscure, I can provide more data if need be. I was just looking for general ideas. 

           Thanks for your help in advance.

           Scott

           FMP 13 adv

        • 1. Re: Importing and storing data
          philmodjunk

               1) Import records can import the data from an excel file into one or more tables in your database. This can be automated with a script.

               2) Import records can also copy data from one FileMaker table to another in the same file, but you can also put all of this data in one table and set up layouts with scripts or a portal that only lists data in a given date range such as just the records for the most recent month.

               Records more than 6 months old could be found and deleted from your table or you could first export them to an "archive" table so that you have the data in a FileMaker table if you later find that you need to reference the data in FileMaker.

          • 2. Re: Importing and storing data
            SomeFileMakerNoob

                 So, basically import with a date range then in the layout portals create a script that uses (I would assume) a Case function to sort and display the data for the desired months. I'm pretty sure I've seen how to do that here in the forums, I'll look them up and see what I can figure out for now. 

                 I have a second scenario now. My DB contains a vendor file with a vendor code, ie., vendor code 004 for Main Street Bakery, and each customer is tied to a specific vendor. Now, each imported excel contains customer ID along with other data. How would I go about adding customer numbers that don't cross-check with the customer database and assign them a vendor code, say 000, for unknown vendors?

            • 3. Re: Importing and storing data
              philmodjunk

                   "import with a date range"?

                   You are mixing up to parts of the system. You would use Import records to import the data. A find--either manual or scripted could be used to pull up only the records for a specified month if you choose to keep all 6 months of data in a single table.

                   

                        I have a second scenario

                   Does that mean that you have two tables Vendors and Clients? And they are linked in a relationship by VendorNumber? And you are importing Client data?

              • 4. Re: Importing and storing data
                SomeFileMakerNoob

                     Sorry Phil, The excel I would be importing doesn't have dates attached to it. So I was thinking as I imported the records I would need to auto-enter the date they were imported. Then I could use this to sort the data by month. Would this be a correct way of doing it? Once the data is imported from the excel it won't be imported again until the next month.

                     As of now, I have 1 table for vendors and 1 for clients and have them linked by VendorNumber. We would indeed be importing customer data.

                • 5. Re: Importing and storing data
                  philmodjunk

                       Yes a date field can be set to auto-enter a date as part of the import if you enable the auto-enter options during that import. You can also use Replace field contents to assign a date to a field immediately after the import.

                       As of now, I have 1 table for vendors and 1 for client...

                       I'm confused by this sentence:

                       

                            How would I go about adding customer numbers that don't cross-check with the customer database and assign them a vendor code, say 000, for unknown vendors?

                       Customer numbers and vendor numbers are not the same thing. And what do you mean by "don't cross check"?

                       Do you mean that you want to change the value of the Vendor Number field if the vendor number doesn't match to a record in Vendors?

                  • 6. Re: Importing and storing data
                    SomeFileMakerNoob

                         Ok, I'll break down the DB some more to hopefully clear up some of my questions. I have the following tables:

                         Vendors - contains vendor information (name, address, phone numbers) along with a unique Vendor ID which we call a VendorCode.

                         Customers - contains typical client data along with a unique Customer ID. Customers are linked to their Vendor using the VendorCode.

                         Charges - This table is where we import the excel file which contains the Customer ID, and 3 other fields with give a breakdown of their charges. This table is linked to the Customers via the Customer ID. 

                         UpCharges - This table is the same format as the Charges table and has it's own excel file which is imported every month. This table is linked to the Customers table using the Customer ID.

                         So, the way I have things now, is I have a Customer Layout which displays the Customer information and using 2 portals displays the data tied to their Customer ID from the Charges and UpCharges table. My original question was to have the most recent imported data displayed in this layout but allow the previous months displayed in a "History" type layout. Still working on getting that part completed.

                         My question regarding the cross checking is hopefully cleared up a little more with the details of the DB. We receive the Charges and UpCharges excel's from another company and sometimes they have Customer ID's assigned to them which aren't in our DB. So what I was wondering is if there was a way to, I guess validate them against our Customers table, and if the Customer ID isn't matched to any in our Customer Table add them to the table and make their Vendor ID "000" for Unknown Vendor. This would be in place to make sure that any data that isn't tied to one of our current Customer's ID is still accounted for and we can contact them to find their vendor info. I hope this cleared things up for you. 

                          

                         Thanks.

                    • 7. Re: Importing and storing data
                      philmodjunk
                           

                                if the Customer ID isn't matched to any in our Customer Table add them to the table and make their Vendor ID "000" for Unknown Vendor.

                           I'll outline this for Charges and leave it to you to apply the same method for Upcharges.

                           On a layout based on Charges, you can enter find mode and put an asterisk in the Clients::ClientID field to find all Charges records that have a related client record. But you want the opposite, so click the Omit button while in find mode to find all records that do not have a matching client record. You can then use Import records to copy client data from this found set into your clients table and as I previously mentioned for a date field, replace field contents can update the vendor number field of this found set of imported records.

                           For your first issue, take a look at the scripted find examples found here: Scripted Find Examples

                      • 8. Re: Importing and storing data
                        SomeFileMakerNoob

                             Ah, hell. I didn't even think about using Omit. 

                             Phil, I appreciate your patience and your help. I'll give this info a try and see what I can figure out.