| Author |
Message |
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
   Posted: 26 Jan 2011 at 2:49am |
|
thank you. i hate to sound stupid, but i am so new at this. how and where would i enter this command? and would i link to both files or just one ?
|
 IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 26 Jan 2011 at 3:17am |
In the Database Expert, you would replace both tables with a command that selects everything you need for the report. To add a command, open the connection on the left side of the screen and look for "Add Command". If it's not there, then the type of database you're using doesn't allow commands. I can't think of a different way of getting the data in the format that you need, though.
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 27 Jan 2011 at 7:14am |
Thank you SO MUCH! I think I'm headed in the right direction now. Still some bugs to work out, but that seems to do the trick of combining the two tables! Now I want to somehow ADD to the inventory on hand if it's a purchase order and subtract if it's a work order. keep a running total by date. any suggestions ? I really appreciate all your help!!! 
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 27 Jan 2011 at 11:05am |
Because you just need the starting inventory, this is going to be a little complicated, but it is doable.
1. Add a string to each Select in the union query that will identify which table the data is coming from. This is as simple as adding something like the following to the PO Select
'P' as TableType
and this to the WO Select:
'W' as TableType
2. While you're editing the command, also add the inventory quantity to each of the select statements. This will bring in the same value for all records for each part, but that's ok - we can work with that. For this example I'm going to use the field name "QtyOnHand" for this data.
3. Create a formula that can be used to summarize the quantity changes. I'm going to call this {@QtyForInventory}. It will look something like this:
If {command.TableType} = 'P' then {command.qty} else ({command.qty} * -1)
4. If you need just the final quantity, create a formula that looks like this:
{command.QtyOnHand} + sum({@QtyForInventory}, {command.PartNumber})
Let me know if you need a running total instead, that will take a little more work.
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 28 Jan 2011 at 5:20am |
Hello, yes I'm looking to have a running total on each line. for the first record I want to add or subtract to the beginning qty on hand, and then on the next line I want to add or subtract from the result of the previous record, and then continue the same on the next line... for example:
Part No. 2041
Qty on Hand 1,421
work order/po# qty date Inventory Balance
25633 2098 1/1/2011 3519
8407 100 3/28/2011 3419
8410 20 3/28/2011 3399
I can't figure out how to do this. Any help you could give me would be greatly appreciated, you have been so awesome already! :-)
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 28 Jan 2011 at 5:28am |
p.s. This is how my command looks now, how would i change it? i tried adding the p and w for tabletypes but got an error message.
Select PART, PURCHASE_ORDER as ORDERNO, DATE_DUE_LINE as ORDERDATE,QTY_ORDER as QTY from V_PO_LINES
Union
Select PART,JOB as OrderNumber,DATE_DUE as ORDERDATE,QTY_COMMITTED as QTY from V_JOB_COMMITMENTS
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 28 Jan 2011 at 7:08am |
What is the name of the inventory table? Does it have a PART field to link to? What is the name of the field that has the qty on hand?
-Dell
|
|
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 28 Jan 2011 at 8:37am |
|
the Inventory table name is V_INVENTORY_MSTR and the part number field is PART
|
|
IP Logged |
|
Kitty1
Senior Member
Joined: 14 Apr 2010
Online Status: Offline
Posts: 159
|
Posted: 28 Jan 2011 at 8:38am |
sorry...the qty on hand field is called QTY_ONHAND
thanks!
|
|
IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
|
Posted: 28 Jan 2011 at 9:26am |
Change your SQL to something like this:
Select
'P' as TABLETYPE,
po.PART,
po.PURCHASE_ORDER as ORDERNO,
po.DATE_DUE_LINE as ORDERDATE,
po.QTY_ORDER as QTY,
inv.QTY_ONHAND
from V_PO_LINES as po
inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART
Union
Select
'W' as TABLETYPE,
jc.PART,
jc.JOB as OrderNumber,
jc.DATE_DUE as ORDERDATE,
jc.QTY_COMMITTED as QTY,
inv.QTY_ONHAND
from V_JOB_COMMITMENTS
inner join V_INVENTORY_MSTR as inv on po.PART = inv.PART
Then you'll need to do the following:
1. Create the {@QtyForInventory} formula as specified above.
2. Group your report on {command.PART}
3. Create a running total (I'll call it {#Inventory}):
Field to Summarize: {@QtyForInventory}
Evaluate: On Each Record
Reset: On change of group {command.PART}
4. Create a formula: {command.QTY_ONHAND} + {#Inventory}
This will display your running total on the inventory.
-Dell
Edited by hilfy - 28 Jan 2011 at 9:27am
|
|
|
|
IP Logged |
|
|
|
