1 2 Previous Next 23 Replies Latest reply on Jul 18, 2014 8:09 AM by philmodjunk

    Problems displaying 1 to 1 fields on a layout

    Beastking

      Title

      Problems displaying 1 to 1 fields on a layout

      Post

           Hi,

           Am using Filemaker 12 and wanted help on displaying fields from one of my tables in a more condensded format.

           My records are imported weekly from excel files the format of these files cannot be changed.

            

           The issue I have is with a 1 to 1 record. The child has about 400 fields each displaying quantities and I would only like to display the feilds (&fieldnames) on my layout that have a non zero value typically no more than 15 for each record.

           My solution currently is before importing the records is to run an excel macro that reorganises the child table records so they effectively it has become a 1  to many relationship then I import these into another table and is shown on my layout via a portal. So I have 2 tables in the 1 database with the same data. Which means when I change records I have to change both locations.

           I would rather do everything thru filemaker and remove my excel fix  out of the ocassion.

           I don't know the best way to do this but thought maybe I can run a script when the importing records script runs to also create these records to a new table so I can then display via a portal.

            

           Or maybe is another option? Can the position of fields on a layout be shifted based on value?

            

            

            

        • 1. Re: Problems displaying 1 to 1 fields on a layout
          philmodjunk

               A better option would be to import this data into a temporary table (one used only to receive imports from these excel files and then use a looping script to parse this data into a set of related records  where each record represents one product purchased with a nonzero quantities. (columns where the quantity is zero do not result in adding a record to this table)

               You can then use a portal (or the horizontal portal technique) to display this data and you will only have portal rows for those quantities that were not zero.

          • 2. Re: Problems displaying 1 to 1 fields on a layout
            Beastking

                 Hi PhilModJunk,

                  

                 Thanks for the reply

                 Total greenhorn when it comes to loop scripting. Best plan of attack advice needed please? I.e. So picking would have to have a loop for each field and if true then it perform a another script that would write/SET the related fields for me?

                 Nb: Its possible for me to have records that all values = zero hence you wouldn't need to write a new record in the new table, maybe I can sum the total of all the value fields and if it equals zero I would just move to the next record (at import stage there is no negatitive values). All do i just let the process happen?

                  

                 Cheers

            • 3. Re: Problems displaying 1 to 1 fields on a layout
              philmodjunk

                   I may be wrong about what you have so let's confirm some details before tackling specifics:

                   Your excel file looks something like this?

                   InvoiceID    ProductA Qty    ProductB Qty    ProductC Qty  (and so forth for many product quantities.
                   1234             23                             0                       5

                   For best use in fileMaker, we want to take that data and produce a table like this:

                   A table of LineItems:

                   InvoiceID     Product             Qty
                   1234                     A                 23
                   1234                     C                 5

                   Note that no record for Product B was created as it had a qty of zero.

                   And from this row you would also likely create a Parent record in a table of invoices with an Invoice ID of 1234 to link to each record with that number in line items.

                   While some minor details may vary between my example an your spreadsheet, is the basic idea the same or have I misinterpreted?

              • 4. Re: Problems displaying 1 to 1 fields on a layout
                Beastking

                     Yes your example is spot on.

                      

                      

                • 5. Re: Problems displaying 1 to 1 fields on a layout
                  philmodjunk

                       This will be pretty major script for a "greenhorn". Make lots of back up copies of your file and be prepared to do a lot of trial and error and feel free to post back here with questions.

                       You'll need two tables linked in relationships like this:

                       Invoices::InvoiceID = LineItems::InvoiceID (Since InvoiceID comes from a source outside of your database, this is not ideal, but let's get the basic set up to work first before discussing a better way in which to link the records.)

                       A third table, Import should be linked to LineItems like this:

                       Import::constOne = LineItems::CreateKey

                       Define constOne as a calculation field with a number result type and 1 as the sole calculation term. Double click this relationship line and select "Allow creation of records via this relationship" for LineItems. We'll use this special relationship as a way to generate new LineItems records without having to change layouts.

                       I'm going to assume that you will be importing into a table with fields named exactly as I've shown in this example:

                       InvoiceID    ProductA      ProductB    ProductC  (and so forth for many product quantities.
                       1234             23                    0                5

                       We'll need a layout basd on this table with the fields arranged in this order so that when you press the tab key, it first enters InvoiceID and then tabs from column to column from left to right without skipping any fields. In my example, I will stick with just 3 Product columns and leave it to you to extend this to the number of columns in your spread sheet.

                       Immediately after importing this data, the imported data will form  a found set. We'll use two loops, one inside the other. The outer loop will loop through each of these records (rows) and the inner loop will loop through the fields (columns) to create the individual records.

                       Go to Record/Request/Page [First]
                       #Loop Through the rows
                       Loop
                          Set Variable [$InvoiceID ; value: Import::InvoiceID
                          #Create parent record in Invoices
                          Go to Layout ["Invoices" (Invoices) ]
                          New Record/Request
                          Set Field [Invoices::InvoiceID ; $InvoiceID ]
                          Go to Layout [original layout ]
                          #Loop through the columns
                          Go to Field [Import::ProductA]
                          Loop
                             If [ Get ( ActiveFieldContents ) > 0 // A Qty is specified for this product ]
                                Set Field [ LineItems::ProductName  ; Get ( ActiveFieldName ) ]
                                Set Field [ LineItems::Qty ; Get ( ActiveFieldContents ) ]
                                #Link it to the new parent record in Invoices...
                                Set Field [ LineItems::InvoiceID ; $InvoiceID ]
                                #Disconnect new record from special creation link so that it can be used to create another new record
                                Set FIeld [LineItems::CreateKey ; "" ]
                             End IF
                             Go to next Field
                             Exit Loop IF [ Get ( activeFieldName ) = "InvoiceID" ]
                           End Loop
                           Go to record/request/page [next ; exit after last ]
                       End Loop

                       Testing and development notes:

                       Lines starting with # are comments. There's a script step for them.

                       Text to the right of // are also comments, but are comments "embedded" inside a calculation. All text to the right of // is ignored in a calculation.

                       Test this out with a test import of just a few records.

                       If you get a script that runs and runs but never seems to end, one of the Exit loop options is not working. You can abort this script by pressing Esc (windows) or Command period (macs).

                       FileMaker Advanced's script debugger is invaluable when working with such complex scripts as you can step through it one step at a time while checking the values of fields and variables.

                  • 6. Re: Problems displaying 1 to 1 fields on a layout
                    Beastking

                         Thanks for the reply.

                         Sorry a little lost. Firstly a check on the basic structure I need here.

                         I have my 3 original tables  - main table, invoice & invoice 2 (created from excel macro - soon to be redundant hopefully)

                         I need to create 2 new tables?

                         LineItems with the fields; InvoiceID, CreateKey, ProductName, QTY?

                         Plus also need to create a new table import but with what fields? constOne is one set as calc field  - but not sure if anything else and what is the significance of this field?

                          

                         Now I am a little lost trying to understand the script

                         We need to set the variable InvoiceID with field from Import::InvoiceID which I don't have predefined, as above think i am missing fields for import table.

                         Then the go to layout requests just want to clarify what layouts i need to be going to - invoices - is this the layout for what you have described above? And go to original layout would not the described layout suffice? 

                          

                         CHeers

                          

                          

                          

                          

                    • 7. Re: Problems displaying 1 to 1 fields on a layout
                      philmodjunk
                           

                                I need to create 2 new tables?

                           I don't know. what do the three tables that you have defined do?

                           A typical invoicing database has these table in these relationships:

                           Customers----<Invoices-----<LineItems>-----ProductsAndOrServices

                           Customers is one record for each customer that has purchased products and/or services from you. It may not be needed if you don't track data on customers.

                           Invoices is one record for each sales transaction.

                           LineITems is one record for each product or service listed on a given invoice. You can have one Invoice linked to many line items in order to list all of the products or services purchased on that invoice.

                           ProductsAndOrServices is your "catalog" of products and/or services that you offer to your customers. Data such as description and price are recorded in this table.

                           Whether you need all or only some of those tables will depend on what you need to do with your database and the data you import into it. I've assumed that you'll need at least two tables, a table of Invoices and a table of LineItems, but these are arbitrary names so you may find that you already have some of the tables needed here.

                      • 8. Re: Problems displaying 1 to 1 fields on a layout
                        Beastking

                             Hi PhilModJunk,

                              

                             Thanks for the reply, my existing database works fine and is not a typical invoicing database. I have no need to even try to make this a typical invoicing database.  All I want to be able to do is display the valued fields on my layout in a condensed format. Currently I use an excel macro to do this.

                             The invoicing is irrelevant here my bad i should have made this clearer from the start the query is all about the product.

                             So I think we need to look at this more simplistically. I.e. I have my product  table as in what u had described above

                             ID           ProductA Qty    ProductB Qty    ProductC Qty  (and so forth for many product quantities.
                             1234             23                             0                       5

                             I want this so i need to create;

                             A table of LineItems:

                             ID                    Product             Qty
                             1234                     A                 23
                             1234                     C                 5

                             So all I have currently is the product table that contains all the data I need for this script. And I need the script to fill the Lineitems table with the fields; ID, CreateKey, ProductName, QTY?

                             So in what i don't understand ;

                             1/  A third table, Import should be linked to LineItems like this:Import::constOne = LineItems::CreateKey

                        Define constOne as a calculation field with a number result type and 1 as the sole calculation term. Double click this relationship line and select "Allow creation of records via this relationship" for LineItems. We'll use this special relationship as a way to generate new LineItems records without having to change layouts.

                             I don't have this table not sure what do i need to create here or even if i have too

                             2/  the referencing in a lot of the script steps as these references don't relate hence the confusion.

                              

                             Please help

                             Regards

                        • 9. Re: Problems displaying 1 to 1 fields on a layout
                          philmodjunk

                               The table into which you import your data matches the structure of your excel spread sheet. But since it does not match the structure of the resulting table we need to produce ( in line items), you need both tables linked in this relationship. And this is a special relationship only used for creating new records in the line items table without having to change layouts to do so. (Changing layouts screws up which field has the cursor and would thus keep the loop with go to next field from working correctly.)

                               ID           ProductA Qty    ProductB Qty    ProductC Qty  (and so forth for many product quantities.
                               1234             23                             0                       5

                               Describes the structure of that table. Since I didn't know the name of that table in your database, I referred to it as "ImportTable".

                          • 10. Re: Problems displaying 1 to 1 fields on a layout
                            philmodjunk

                                 BTW, even though you do not use this database for "invoicing" it's possible that a table of Invoices could be useful for some reporting tasks you might choose to do with your data. From the beginning I suggested that you might find such a table useful.

                            • 11. Re: Problems displaying 1 to 1 fields on a layout
                              Beastking

                                   As strange as is sounds the "invoicing" is not related to the product. Its a 3rd party type situation so its unnecessary and will only complicate things.

                              • 12. Re: Problems displaying 1 to 1 fields on a layout
                                philmodjunk

                                     That's your choice, but you did agree that there was a column called "InvoiceID" in the data being imported. That suggests multiple records being imported with a common value in the InvoiceID field. If so, there may be reporting tasks that benefit from a related table of one record for each invoice, but you do have the option to leave out this part of the solution I described. Leaving it out does not keep the rest of the process from working.

                                • 13. Re: Problems displaying 1 to 1 fields on a layout
                                  Beastking

                                       Have tried to get to go but I think the result is still getting affected by what is a relationships issue. I still don't know if they are right. as we seem to be getting sidetracked with the invoice table issue.

                                       So moving on

                                       I have 2 tables --> lineitem table and product table which are linked by id i.e.

                                       Product::ID = LineItems::ID

                                       And the special relationship you advised me to do.

                                       Import::constOne = LineItems::CreateKey

                                       So the import table as you have called is also my product table. Are we suppose to have 2 realted fields here? 

                                       Please advise the relationship required to make this work

                                       The script as i have it at the moment

                                       Go to Record/Request/Page [First]
                                       Loop
                                          Set Variable [$ID ; value: Import::ID]
                                          Go to Layout ["Lineitems" (Lineitems) ]
                                          New Record/Request
                                          Set Field [Lineitems::ID ; $ID ]
                                          Go to Layout [product ]
                                          Go to Field [Import::ProductA]
                                          Loop
                                             If [ Get ( ActiveFieldContents ) > 0]
                                                Set Field [ LineItems::ProductName  ; Get ( ActiveFieldName ) ]
                                                Set Field [ LineItems::Qty ; Get ( ActiveFieldContents ) ]
                                                #this one would seem to be an issue as have already written about makes no difference if I take out

                                                Set Field [ LineItems::ID ; $ID ]
                                                #Disconnect new record from special creation link so that it can be used to create another new record

                                                #this line seems to be the issue as possible incorrect relationship
                                                Set Field [LineItems::CreateKey ; "" ]
                                             End IF
                                             Go to next Field
                                             Exit Loop IF [ Get ( activeFieldName ) = "ID" ]
                                           End Loop
                                           Go to record/request/page [next ; exit after last ]
                                       End Loop

                                        

                                       Cheers

                                  • 14. Re: Problems displaying 1 to 1 fields on a layout
                                    philmodjunk
                                         

                                              So the import table as you have called is also my product table. Are we suppose to have 2 realted fields here?

                                         No. And I am puzzled by your use of an ID field here. why would you link a record of this reported data to the records in the line items table? How do you plan to use that relationship. (it is not needed in order for this process to work.)

                                         If you have a link by an ID field and by the "create" relationship, you need either two table occurrences of LineItems or two table occurrences of Products so that you can set up two separate relationships. When you match records by two pairs of fields, you get an And relationship where the values in the two fields in one table both have to match to records in the other.

                                         So you'd need either:

                                         Products|Create----Linetems>-----Products|ID

                                         Where Products|Create and Products|ID are two occurrences of the same Products table or

                                         LineItems|Create-----Products------<LineItems|ID

                                         Which is the better set of relationships to set up will depend on you plan to use that relationship that matches by ID.

                                    1 2 Previous Next