Database Design how to link 2 tables


#1

I’m new to Phocas and I’ve created a new database that is for documented cost savings that gives me a savings total by customer, supplier, rep, ect. I also need to pull the invoice data from a separate table.

Some of my dimensions are pulling the savings data and sales data and others are not. I’m not sure we linked the tables properly.

I’ve watched videos and read documentation and don’t see this addressed anywhere. I’ve tried to add a new stream, but i don’t think that is correct either.

This is a pretty urgent request, my President needs this info yesterday! :slight_smile:


#2

I’m assuming your “Invoice” table has the same dimensions as the “Savings” table. Then it should just be a matter of matching up those dimension. So you’d first layout the design with “Savings” table. Map out the dimensions, Branch, Customer, Vendor, etc. and the appropriate measures from that table. Then switch over to the “Invoice” table, and drag the corresponding dimensions over to the ones you mapped out from the “Savings” table. (Branch to branch, customer to customer, etc.) Then map out any additional measures from the “Invoice” table. Don’t forget to map out a date from each and any needed properties. Then as you’re looking at the data by a particular dimension, it should pull the measures from the appropriate table.

So the only linking really that is done is by matching up those dimensions.

(If your field names match up, you can also shortcut this process by using the “clone and replace” feature as well.)


#3

Find video of the Clone and Replace feature here:


#4

Thank you! We finally figured it out. We’ve only had Phocas for Epcior p21 for a couple of weeks and haven’t gone through implementation yet.