1 2 Previous Next 15 Replies Latest reply on Apr 20, 2016 9:16 PM by bigtom

    need help importing into a portal

    jp98

      i need to import records into a portal in my database but i have come across a number of problems.

       

      i have a table that contains a list of all products with product category, product name, product description etc. etc.

       

      the data i need to import is a list of products that are included in a quote for a customer. however, this data only includes product name, product description and quantity. to use this data i need to run a script that also finds the product category for each product before it imports it into the quote.

       

      currently i have a portal where i can select product category from a dropdown, then a second dropdown beside that displays all products within that category which i can select and once selected it will also display the product description beside it and a price from the table mentioned earlier as well as setting the quantity to one. what i can't work out is how to import into this portal so that it does all the above things.

       

      my thoughts so far have been to import the data into a separate table and then run a few looping scripts from there to find the additional information and then import the records to where they are required and then a final script step to delete all records from this additional table - pretty much just a temporary storage table.

       

      sorry for the extremely long message and for how confusing it is but hopefully you can help me resolve my problem!!!

        • 1. Re: need help importing into a portal
          bigtom

          Where is this data coming from?

           

          If you are importing you generally want to import directly into the table that will hold the records. Depending on how your data is arranged you should be able to get a number of things to populate based on relationships, but scripting will work as well.

           

          If you have a screen shot of your relationship graph that will help.

          • 2. Re: need help importing into a portal
            BruceRobertson

            There is no such thing as importing into portals.

            A portal is based on the table that you have declared the portal is based on.

            As mentioned by bigtom - you need to import directly into that table.

             

            But it sounds like you don't actually have a grasp of how the tables in your system relate.

            And - as also mentioned by bigtom - we won't either; until you show us the graph or a more detailed description of the tables in your system.

            • 3. Re: need help importing into a portal
              jp98

              the database has been created over a number of years so the relationships table is massive and i am only carrying on work on this database from where someone else left off.

               

              i was just hoping their might be a way to import the records quickly in the same way that you can create individual records in the portal.

               

              i am still working out some of the relationships myself but i will explain these are little clearer shortly.

              • 4. Re: need help importing into a portal
                CarstenLevin

                Hi jp98

                i was just hoping their might be a way to import the records quickly in the same way that you can create individual records in the portal.

                It is correct what Bruce and Big Tom says.

                 

                But there is a quick way to do it: Import the records into the table that is behind the portal.

                 

                But when you say that you want to import into the portal, I assume you want the records related to the present record that you are viewing (like when you enter data via the portal), is this assumption correct?

                 

                If it is, then you will need to add the foreign key to the records you just imported. The foreign key here will be the one that you find in the table of the record you start with (where the portal is). It is the primary key of that table you need to make the foreign key of the other table.

                 

                Question 1: Do you know about the relational model and databases?

                Question 2: How much do you know about FileMaker?

                 

                Just hold on here, by letting us know what your database background is and by answering the questions people here will find out just exactly to help you. And if this is your first encounter with a database, do not fear, help is near:-)

                 

                Best regards


                Carsten

                • 5. Re: need help importing into a portal
                  bigtom

                  The import can be quick and you can automatically create the relationship to the parent record with the right approach.

                   

                  What does the data look like and where is it coming from? Excel? Email? FileMaker?

                   

                  You would need to provide some information for us to help you along. The section of the graph that shows only the tables you are working with would help.

                  • 6. Re: need help importing into a portal
                    beverly

                    bigtom has some great questions!

                     

                    A screen shot of a small part of the relationship graph can be taken or better yet, take a screen shot of the relationship detail (the connector between the two table occurrences).

                     

                    All have let jp98 know that import works into a layout with one table (or table occurrence) upon which it is based. Not all fields need be on the layout for the import.

                     

                    As for the "lookup", it really would depend on what other relationships might have that information.

                    post the relationship for the portal first and we go from there

                     

                    beverly

                    • 7. Re: need help importing into a portal
                      jp98

                      thanks for your suggestions everyone - see below images of relationships. the table Quotes is the one that i am on and the portal displays data from the related table Quote Invoice Lines

                      screenshot of relationship.png

                       

                      this second relationship shows the relationship between the Quote Invoice Lines table and the z_stock for Quote via UID table which is for doing the extra lookup in our cost matrix for extra product information.

                      screenshot of relationship 2.png

                      hopefully this is more helpful

                       

                       

                      in response to bigtom re the data - it is coming from excel and it has all been sorted using vbscript into columns with product code in the first column, product description in the second and quantity in the third so is very simple to import or loop through each row.

                       

                      to carstens questions 1 and 2 - a bit about both but haven't had the experience that all the rest of you have had

                       

                       

                       

                      however, i still don't know exactly how to use this information when importing the data. is it right that its something along the lines of creating a variable and getting the quote uid from the current quote that you are accessing and using that when importing?

                      • 8. Re: need help importing into a portal
                        jp98

                        there are also these two relationships below - second one will probably be of use - don't know about the first one.

                        in this one we are linking the Quote Invoice Lines table to another table that is just shown in the relationships and is linked to a table called Deliveries

                        screenshot of relationship 3.png

                        this second one is again between Quote Invoice Lines and Cost Matrix with the cost matrix table being called z_Stock for Quote via Material

                         

                        in this, material is actually product code, and cat is obviously product category

                        screenshot of relationship 4.png

                        • 9. Re: need help importing into a portal
                          bigtom

                          So this is being done from a Quote record.

                           

                          You want the product IDs in the Quote Lines tables.

                           

                          There are some other bits that need to be looked up, but how exactly are they referenced?

                           

                          To import and relate you might try importing to the proper table directly, but adding a global $$ImportID=(Get(UUID)) and an extra field in the table where the productIDs land, "ImportID", that is auto enter calc to use $$importID. Set the pkID from the quote in a variable $quoteID. After the import is done you find the records with $$ImportID in the ImportID field. Start from the first and loop through them setting the quoteIDfk(Quote Record Number?) with $quoteID. This will create the relationship.

                           

                          The other stuff will need a little more details.

                          • 10. Re: need help importing into a portal
                            jp98

                            bigtom wrote:

                             

                            To import and relate you might try importing to the proper table directly,

                            do you mean into the quote invoice lines table or the quotes table

                             

                            $$ImportID=(Get(UUID))

                            and what is the UUID that we are wanting to get here?

                            • 11. Re: need help importing into a portal
                              bigtom

                              Yes. I think so.

                              • 12. Re: need help importing into a portal
                                bigtom

                                The UUID is a new unique ID for that specific import process.

                                • 13. Re: need help importing into a portal
                                  jp98

                                  i managed to get it working after trying a few of your steps and with the relationships that were already setup. Thanks very much for your help everyone!

                                  • 14. Re: need help importing into a portal
                                    jp98

                                    for the UUID that you suggested - i came up with an alternative where the import id field for the records is set to one and then cleared at the end of the script. this works really well as i can perform finds in that field for any records that have one in them and know that they are the only ones that have just been imported.

                                    1 2 Previous Next