Extra line being populated in portal

[jeanine]

Hi,

 

Very much a newbie here.

 

I've created a PO/invoicing system, or am attempting to. Per most of the examples I've seen, I have a Portal in my PO layout for a Line Items table, which pulls from a Products table. The LineItems table contains the fields Item Description, Item ID Number, Discount Rate, Quantity, Taxable, Unit Price, Amount, Amount Taxable & Discount Amount. The records displayed in the portal on the PO layout are Item ID, Item Description, Quantity, Unit Price, Amount.

 

When I type an item number into the field, it populates the description. It does not display the unit price, when I type in a quantity it correctly calculates "Amount". Then, it automatically populates a second line in the portal with *only* quantity and amount, which of course messes up my subtotals, etc. I have no idea why it's doing this.

 

So, I need to display the Unit Price and make it stop double-populating certain fields, affecting my totals.

 

Also glitchy, sometimes when I type in a second item # on the next row, after tabbing or clicking outside the portal it changes that number to the first item # in the portal.

 

I've attached PDFs of my relationship graph & the PO layout, in case they're helpul.

 

Many many thanks for any help you can offer!relationship graph.pdfUntitled.pdf

[Lee Smith]

Why don't you just post a copy of your file?

[jeanine]

Do I need to start a new post to do that?

thanks

[LaRetta]

Hello Jeanine, welcome to FM Forums! 

 

I suggest that you step back and restructure using proper relational keys or you will have to change it eventually anyway.  You do not want to join on name - name can change and then your relationships will break.  EVERY table should have a unique primary key (called the Primary key or parent key).  It is usually named 'theTableName' and ID, such as ProductID; some name it pk_ID and other conventions but I prefer plain english as ProductID or simply ID (in the primary table).  At the field definition, create your ID and then at the auto-enter tab, specify 'serial' increment by 1.  Now that you have proper IDs in all tables, create your foreign keys (also called child keys).  

 

Child keys go into the table which is (normally) the 'many' side.  When you connect these keys, the proper relationship is established.  The data type should *match for your keys (both sides number for example).  If you wish to relate a Product to a LineItem you will need to connect ONLY THE KEYS (remove the description).  Keys should be unique, never empty and be meaningless.  Check the fields in the portal.  Be sure that, in this instance, they are all based upon the same table occurrence name as the portal itself (double-click the field in layout mode to bring up its field box).

 

So your relationship would be:  LineItems::ProductID = Products::ProductID

 

For selection of a Product in your LineItem portal,  create a value list of Products  (all products in field values).  At the next value list entry screen, select the primary key in the left pane (Products:;ProductID) and in the right pane, select the product's description (this can be a concatenated calculation in the case of customers).  Attach this value list to your LineItems::ProductID field.

 

Also, remove the spaces from your field names.  The odds are high that they will eventually cause you great grief.  Instead you can use camel case as BillToAddress or underscore as Bill_To_Address.

 

Once this is all done and your data has these keys, it should work as you expect.   If you get stuck, we are here to help.  This basic understanding is important for you to move forward. If you correct the file and THEN post it, we would be happy to verify that you are on a solid track to move forward. 

 

*multiline keys are the exception where the multiline side must be text.

[LaRetta]

Also, you have the ProductID in Purchase Orders.  Purchase Orders is the ONE side to MANY LineItems so you put the parent key (Purchase Orders ID) into the LineItems table.  So when you create a new LineItem (if you have checked 'allow creation' in the LineItems table graph), the Purchase Order ID will be automatically inserted into the LineItem records.

 

In this way, your many LineItems will relate to the single Purchase Order (put the parent key from the ONE side into the child table MANY side).  Your LineItem Price should be a lookup from Products (set it up in your field definition on the Price.  This should provide you with a good start towards understanding relational design.  There is a great link to Comment's sample file showing perfect invoice structure but I cannot find it on this current device.

 

Just ask questions as you need help.  This is a wonderful website and folks here are most helpful.

[comment]

There is a great link to Comment's sample file showing perfect invoice structure but I cannot find it on this current device.

 

 

http://fmforums.com/forum/showpost.php?post/309136/