Relational Data Help

[MurilloReis]

Hi,

This is my first post here and I have a problem with a relational data as shown in the img below.

Written problem: I am trying to relate one price field from the inventory layout and relating into the sales sheet layout. now the only way i would know how to do this (wich doesnt work) would be

ITEM NAME is Related from the layouts INVENTORY and SALES SHEET, and the item prices 1 2 3... and so on is Related :: so if ITEM NAME IS = INVENTORY AND SALES then RELATE SALES PRICE

But that doesnt seem to work, i beleive that the price relations would have to be separate to work but i cant have them separet.

So if anyone can help me with this i would be very greatfull and if i can in some way help out also i would be more then happy to.

Thanks for your concern

img explanation:

[David McQueen]

Not too sure what you are trying to accomplish here. Here is how I would create sales records so that you can relate them back into inventory.

HTH

dave

When you create a sales record in the sales table, have the process go as follows:

1. Select inventory item from the inventory table.

2. Have record created in sales table

3. Have an Inventory Key field in the sales table. Populate it with the value of the prime key from the selected item in the inventory table.

4. You want a relationship back from Sales to Inventory:

Inventory Key (Sales Table) ->Prime Key ( Inventory Table)

You can use that relationship to suck in the rest of the data in the sales record.

You have also uniquely identified the sales record to an inventory record.

[MurilloReis]

Thank you dave,

I know i wasnt to clear on what i wanted before. and since i am only a begginer i also didnt quite get your formula, but to be clearer i uploaded my exact situation on filemaker and i think that you can get a better idea of what im looking to accomplish.

As you can see, in the img Untitled-3 I have the relation from the first item in the inventory with the price, now if i were to relate the second record in the inventory to the second field in the sales sheet then i cant relate the second price field because it wount work.

im trying to relate each record from the inventory to each field in the sales sheet.

(also i am sorry about the language this is a brazilian company)

Tell me if this helps!

thanks again

Murillo

[comment]

It seems you are missing a table for the sale line items - see the demo here:

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

[MurilloReis]

Im still having a hard time figuring out how this is formulated, but, it is my interest to have a report on wich items went to whom and for how much, and i see that this file uses a portal to get the information stored on the sales list. now if i were to get a report on a certain item on the portal for that cliente how would that work?

im sorry that im so confused, but i think i need a more detailed and specific information on what i need to do...

if there is any other information that you need let me know !

Thanks for your concern.

[MurilloReis]

I would guess that i am looking for a "scripted" field that would say

[color:blue](field) SALES PRICE 1 - [if item name{from layout inventory}= item name{from layout Sales} Relate Sales Price{from Item price on inventory layout}

[color:blue](field) SALES PRICE 2 - [if item name{from layout inventory}= item name{from layout Sales} Relate Sales Price{from Item price on inventory layout}

[color:blue](field) SALES PRICE 3 - [if item name{from layout inventory}= item name{from layout Sales} Relate Sales Price{from Item price on inventory layout}

.... and so on!

[David McQueen]

It is hard to tell what you are doing. Here is basically what should be happening in terms of structure:

1. There should be an Inventory Table. This is the place that inventory is entered.

2. There should be a sales table. This is where data from sales is entered. There will be one record per sold item. This is many times referred to as a line items table or line items file if you are using older versions of FMP.

If your are doing invoices of many items, this would be anther table bringing together the Line Items in a sale.

3. The inventory table should have on field that can be either text or number which is indexed and which is unique for each record. This is the prime key for this table. Similarly the Sales (Line Items) table will also have a Sales Prime Key field.

4. The Sales table should also have a field that we will call Inventory Key. When a sales record is created, that field is populated with the value of the Prime Key from the inventory record that is used to create the sale record.

Now with this established, you can then relate the Inventory table to the sales table and read all of the sales made from that inventory lot.

I am speaking in general terms because there a lot of different ways people could have this structured. But these items are the key items to getting a relational inventory/sales solution.

HTH

Dave

[Fenton]

Basically, never use multiple fields in one record for something that has multiple instances. Field1, field2, field3, etc. is the worst structure; nothing can be done with it. This is why you cannot make it work. Follow David's advice.

I am moving this topic out of this special forum to relationships.

[comment]

if i were to get a report on a certain item on the portal for that cliente how would that work?

I am not sure what exactly you mean by that. In general, once you have a line items table where each sale of an item is an individual record, you can produce a report from any point of view you choose. There's an example in my demo file that produces a report of one invoice's items (see the script "PrintOneInvoice"). If you wanted, you could produce a similar report sub-summarized by product, or by a date range, etc.

[MurilloReis]

Thank you dave,

I will seek your advice and go after the answers I need, you were of most help and i think i got the idea now.

thank you for everyone that helped me out.

Murillo

[bruceR]

Im still having a hard time figuring out how this is formulated, but, it is my interest to have a report on which items went to whom and for how much

You mention a portal but the images you've posted and some of the descriptions show a really serious design problem. You don't have a relational database at all.

You have fields like Status1, Status2, ProVendido1, ProVendido2.

There should be NO fields named this way and I'm pretty sure you're going to need to do a major redesign. You need to get some help understanding data relationships.

[MurilloReis]

Would it be too much to ask for the way to find those informations to understand data relations a little deeper?

On the images i sent you did not have the portal with the relation. i am now testing the portal way of relating data and i kind of get it.

although i am still having some dificulties on the portal relation i have a more clear heading of what i am aiming toward.

If you have any resources that you think might help me i would be very greatfull.

Thanks for your time

Murillo

[David McQueen]

Hi Murillo

Here are two links to books on Amazon. These will give you more information on FileMaker Pro than you ever wanted to know. They will give you a sound background in the program and databases in general.

I have purchased previous versions of these two books with every major revision of FileMaker. Hopefully the links will come out useable when posted.

Dave

FileMaker Pro 9 Bible

http://www.amazon.com/FileMaker-Pro-Bible-Ray-Cologon/dp/0470177438/ref=sr_1_2?ie=UTF8&s=books&qid=1228303451&sr=1-2

Special Edition using FileMaker 9

http://www.amazon.com/Special-Using-FileMaker/dp/078973723X/ref=sr_1_7?ie=UTF8&s=books&qid=1228303451&sr=1-7

[bruceR]

Would it be too much to ask for the way to find those informations to understand data relations a little deeper?

On the images i sent you did not have the portal with the relation. i am now testing the portal way of relating data and i kind of get it.

although i am still having some dificulties on the portal relation i have a more clear heading of what i am aiming toward.

If you have any resources that you think might help me i would be very greatfull.

Thanks for your time

Murillo

I would suggest you start by taking a closer look at things you already have with your FileMaker application.

In particular, look for the Tutorial folder, and follow the complete tutorial. Note "Lesson 10 Making Databases Relational". Also, look at the FileMaker Help menu, where you will find Learning Center and Product Documentation. Look at all the files in the Templates folder.

Edited December 6, 2008 by Guest