1 2 Previous Next 24 Replies Latest reply on May 5, 2014 8:19 AM by philmodjunk

    lookup problems

    HarryFisher

      Title

      lookup problems

      Post

           I am a new user of File maker 12 pro but I am a user of MS Access

           I have imported some data from a spray database

           The 450 products are split into chemical groups.

           When placing an order I have generated a line item table which allows me to select a chemical from the product list.

           I would like the chemical group to be auto entered as  I would like to group line items by chemical groups monthly.

           I do not seem to be able to relate the line items to chemical groups automatically.

           Using Access I did this using queries.

           Should I do this using a look up field (I have tried this for several days without success) and if so where to set it up

           I enclose a screen shot of the relationships.  

           Can you see any problem

            

           Thanks H

      Screenshot_%281%29.png

        • 1. Re: lookup problems
          philmodjunk

               I don't see a table for the chemical groups. I would guess that you need something like this:

               order_lineitem_PRODUCT>-------ChemGroups

               Order_lineItem_PRODUCT::_fkChemGroupID = ChemGroups::__pkChemGroupID

               Then a list view layout based on Order_lineItem_PRODUCT could be used to list the items on one or more invoices and a sub summary layout part could be added to show the name of the Chemical Group and you would sort the records by _fkChemGroupID in order to group them by chemical group. (It's also possible to get an alphabetical order for these groups.)

               PS. Since you are familiar with SQL queries, the ExecuteSQL function (NOT the script step) may be of interest to you. It's not the "full up" SQL based query system that you are used to, but it has its uses in FileMaker.

               Design note: I noticed that you have this relationship:

               order_LINEITEM::_fk_Product = Order_lineItem_PRODUCT::_kp_ProductID AND
               order_LINEITEM::_fk_Product = Order_lineItem_PRODUCT::ChemCode

               This appears to have an unnecessary pair of match fields. Either you should not have ChemCode linked to _fk_Product or _kp_ProductID and ChemCode store identical values--in which case you only need one of the two fields for use in your Product table.

          • 2. Re: lookup problems
            HarryFisher

                 I did have a chem code table but could not get it to work so then went over to a chem code field in the product table generated by a value list (manually entered.)

                 I will try again and show you the results

                  

                 Thanks

                 H

                  

            • 3. Re: lookup problems
              philmodjunk

                   Whether you need the Chem Group table will depend on whether or not you need record any data other than the name for a given chemical group.

                   If all you need is the name or a code, you don't need the added table, a field in Products will do the job. But a field that identifies a group of products should not be a match field to your line items table.

              • 4. Re: lookup problems
                HarryFisher

                     I started to put a chem group table back in but having got your last post have gone back to a field in the products table which identifies a group.

                • 5. Re: lookup problems
                  HarryFisher

                       My goal now is to split transaction no 402 ( a field in the Order Table) so that it totals up the money spent on each chemical group.

                       The answer should look like

                       Cereal Fungicide Total £4113.70

                       Cereal Herbicide Total £3443.00

                       Growth Regulator £1588.45

                       Wetter £227.92

                       Foliar Feed £1184.40

                       Grand total  £10557.47

                       I enclose a couple of screen shots

                       Before I make a hash of it again perhaps you could give me some direction

                        

                       Many Thanks

                       H

                  • 6. Re: lookup problems
                    HarryFisher

                         sorry wrong screen shot here is transaction 402

                          

                    • 7. Re: lookup problems
                      HarryFisher

                           and the relationship diagram

                      • 8. Re: lookup problems
                        philmodjunk

                             If I understand you correctly, you want a single invoice but with the transactions grouped by chemical group with a sub total for each group. Is that correct?

                             If so, first take a look at how the invoices starter solution that comes with FileMaker 11, 12 or 13 prints an invoice. It pulls up a found set of lineitem records an a lineitems based layout (The table is called InvoiceData instead of line items in versions 12 and 13), with fields from the Invoice record in the header and the footer.

                             By printing from such a layout, you can add a sub summary layout part "when sorted by" your chemical group field to group the line items and provide a sub summary "sub head" and/or "sub footer" for each such group. You can use a summary field defined in the line items table to show the sub total for each group if you put that summary field inside the sub summary layout part.

                             If, on the other hand, you want to show a "summary recap" listing just each chemical group and the sub total for it at the bottom of your layout, use ExecuteSQL to produce a small "table" of that information inside a single field: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                        • 9. Re: lookup problems
                          HarryFisher

                               I want to group the chemical groups on Transition No which may have more than one invoice.

                               I will look at the examples as you suggest thanks

                               H

                                

                          • 10. Re: lookup problems
                            HarryFisher

                                 The Invoice DB on from the starter solution is very impressive--  far beyond me at present .

                                 I know how to group now

                                 if I could just get the product field CHEM CODE onto the line item layout from the PRODUCTS table.

                                 How can I do this simply ?

                                 I enclose screen shots

                                  

                                 Thanks 

                                 H

                                  

                                  

                            • 11. Re: lookup problems
                              HarryFisher
                              /files/fa6da9546d/Screen_Shot_2014-04-15_at_13.08.30.png 1002x877
                              • 12. Re: lookup problems
                                HarryFisher
                                /files/c5a8293d15/Screen_Shot_2014-04-15_at_13.09.30.png 861x793
                                • 13. Re: lookup problems
                                  HarryFisher
                                  /files/3e088eb280/Screen_Shot_2014-04-15_at_13.10.04.png 803x234
                                  • 14. Re: lookup problems
                                    philmodjunk

                                         You can enter layout mode and use the field tool to add any field from products (such as ChemGroup) to the portal row (if you have a portal to lineItems) or the layout (if the layout is based on lineitems) and it will correctly display data from products for the selected product in that lineItem record.

                                         But you have several table occurrences of lineItems and several table occurrences of Products. So you need to select the Tutorial: What are Table Occurrences? of Products that is directly linked to that portal or layout's table occurrence for line items from the drop down at the top of Specify Fields before selecting the ChemGroup field that you want to add to your layout.

                                    1 2 Previous Next