1 2 Previous Next 15 Replies Latest reply on Feb 10, 2012 4:10 AM by jball

    Desperately need help!

    jball

      Title

      Desperately need help!

      Post

       I am new to developing any type of database and am working in Filemaker Pro 9.  I have so many problems I'm not quite sure where to begin!  In my company I have to create Purchase Orders, with unlimited amount of line items.  From these I need a "new contract notice", traveler form, invoicing (partial invoices per purchase order) and packing slips to be generated with the same information.  I've created a portal in my PO form to allow many line items, but can't get it to work:(

      I've attached my tables .................any help is much appreciated!

       

        • 1. Re: Desperately need help!
          philmodjunk

          There is nothing attached to your post. A very generalized cry for help like this isn't something that we can easily respond to. Try breaking this down into smaller steps and post specific questions about one step at a time. Whith specific questions, we can then provide specific answers.

          • 2. Re: Desperately need help!
            jball

             Okay, I see my tables didn't post:(  I have a portal set up in my Purchase Order Form that is based on a "line Items" table.  Problem 1:  I need unlimited line items, and it cuts me off at 380.  Problem 2:  The field for "line item #" keeps indexing itself and all records show up as "1".  When I set the field not to index, I loose all the test info I've entered.

            Once it get this solved, I need to find a way to carry it over into 4 other layouts.

            Hope this is a little more descriptive. 

            • 3. Re: Desperately need help!
              philmodjunk

              You can use the controls below Post A Answer to upload a screen shot of Manage | database | Relationships. This cannot be a PDF, it has to be of type JPG, GIF or PNG. Uploaded files with other formats are ignored by the forum software.

              It sounds like you have this basic set of tables and relationships:

              PurchaseOrders---<LineItems>----Products

              PurchaseOrders::__pk_PO_ID = LineItems::_fk_PO_ID
              Products::__pk_Product_ID = LineItems::_fk_Product_ID

              The two fields starting with __pk are defined as auto-entered serial numbers and the fields starting with _fk are defined as number fields.

              With a layout based on PurchaseOrders and a portal to lineItems.

              I need unlimited line items, and it cuts me off at 380

              Did you specify a scroll bar for your portal or are you trying to use a portal setup with 380 or more rows visible at the same time? I'd use a portal that has 15-20 rows but with a scroll bar so I can add more. I also would not attempt to print out or create PDF's of my purchase orders from this layout, but would use a different layout that does not require a portal to print.

              That said, if your PO's frequently exceed a 100 line items in size, you may want to explore different layout options for making such a long list of items easier to work with.

              The field for "line item #" keeps indexing itself and all records show up as "1".  When I set the field not to index, I loose all the test info I've entered.

              I don't quite know what you are describing here. Is line item # defined as a field in the relationship between PurchaseOrders and LineItems. If so, it should automatically enter a value from PurchaseOrders and this should be the same value for every item listed in the portal of a given PurchaseOrder. I think you are trying to use the same field for more than one thing and this is creating the confusion you are experiencing.


              • 4. Re: Desperately need help!
                jball
                /files/eb029ccdfd/Relationships.jpg 1280x720
                • 5. Re: Desperately need help!
                  jball

                   i attached my relationships  i really messed up yesterday, i made a copy of a working database that has reports set up right and tried to re-create my portal in the PO layout, now I can't get it to allow data entry:(  I'm sure I missed a step, and have tried to retrace my original setup, but can't figure out what I did wrong.

                  • 6. Re: Desperately need help!
                    philmodjunk

                    i really messed up yesterday, i made a copy of a working database...

                    A good habit to get into is to select save a copy as at regular intervals and save each copy to a different file name so you have multiple copies of your file. This will enable you to "retrace your steps" and you can always discard your current copy and switch to the previous back up copy if you regret the changes just made. I actually use a set of scripts on my development projects that save copies with unique filenames every 15 minutes. It generates a lot of extra copies over the course of the developoment cycle but has saved my bacon several times when I realized that the change I just made wasn't going to work the way I wanted it to.

                    In order to be able to add new records in the Portal to Line Items on your Purchase Orders Layout, double click the relationship line linking the Purchase Orders to Line Items and select "Allow Creation of Records via this relationship" for Line Items.

                    Given the very long list of items you want to add to each purchase order, you may eventually want to use a different method for adding records to your portal that uses a button and a script to add a new record at the top of the portal instead of adding data after the last item to save having to scroll your portal to the bottom. But let's get the basics working for you first.

                    • 7. Re: Desperately need help!
                      jball

                       okay, now I can enter data:)  The portal size is still not good, i have the portal set up to show a scroll bar, but include 375 lines.  I'm guessing this isn't the best way to go.  and eventually I'd like the line items to carry over into other layouts.  you've been a great help, keep the advice coming:)  It is VERY appreciated:)

                      • 8. Re: Desperately need help!
                        philmodjunk

                        I don't think a 375 line portal will be a very efficient design for you. While you'll want to get a better understanding of FileMaker first, a list view layout of line item records may be easier to work with when you have PO's with such a large number of items purchased. It's also possible to set a filter on the portal so that you can work with and view subsets of your total PO instead of all the line items at once.

                        Line Items are records in a table. You can access these records from other layouts if you set up the layout to refer to that table in one way or another.

                        I suggest that you now examine and compare this demo file created by another forum user to see how it works. It's for Invoicing, but the only difference between an invoice and a purchase order is who gets paid so it's a good model for PO's as well as invoices:  http://fmforums.com/forum/showpost.php?post/309136/

                        • 9. Re: Desperately need help!
                          jball

                           got the portal working!  Changed it to be 10 lines, and the scroll is allowing additional lines to be entered.  The numbering is a glitch, but I can work around it.  my next question.....i had it set up originally with repeating fileds, and a calculation that totalled the value of the repeating fields to give me an overall PO total.  what type of calculation should I be using now?  the "total" field int he portal is working correctly to toatl the line item, but I'm not sure how to implement into the PO total calculation.

                          • 10. Re: Desperately need help!
                            philmodjunk

                            In your PO table, add a calculation field as Sum ( LineItems::totalCost ) with "totalCost" being the field that computes the total of the individual line item.

                            You can also use a summary field defined in the lineItems table to compute the "total of" totalCost. You'll almost certainly want that field later for other layouts, but the calculation field with Sum will update more smoothly when you add/edit items in the portal.

                            Just took a closer look at your relationships.

                            Open Manage | database | Relationships

                            and double click the line between Purchase Orders and LineItems.

                            Then remove the Line Item # and Item # fields from this relationship so that you only link by the PO # field. This is what is causing issues with the value in this field.

                            • 11. Re: Desperately need help!
                              jball

                               Nevermind, my portal is not working:(  I have it set up to 7 lines to fit on the print size, and entered 20 lines into it.......but when i moved to another record and back to the record i was originally in, it only showed 9 lines in the portal, the rest were gone!

                              • 12. Re: Desperately need help!
                                jball

                                 how do i get it to print all items in the portal on the form?

                                • 13. Re: Desperately need help!
                                  jball

                                   'm sure i am getting ahead of myself here, but I implemented the portal into the other layouts that i need it in, and now I'm stuck with my invoice calculations:  attached.  Any advice?

                                  • 14. Re: Desperately need help!
                                    philmodjunk

                                    First things first.

                                    What is the current relationship for your portal? I suggested you make a change. Did you?

                                    1 2 Previous Next