Relational problem, very simple..

[BCpRIA8Smh]

My main problem is, I can't get my head around relational database concepts probably. I've enclosed an example of what i'm trying to do basically. I have a product catalogue in excel on sheet 1. I have several other sheets which are called amazon, pricegrabber, etc. About 95% of the content which it references over to the amazon, pricegrabber, etc sheet is the same as sheet 1. However, there will be extra fields which don't appear in sheet 1 which i will type manually. What I have as a problem in excel is if i delete line 5 on sheet 1 then it doesn't delete the whole line in the amazon sheet. This is why i'm trying to use filemaker. I could just delete the record and it would be completely gone, etc.. However for the life of me, i can't get this to work.. i'm using filemaker 9 pro advanced. I have attached a sample excel file which if someone would be kind enough to make a filemaker database for me it would be appreciated.. if it does what it should do then i'll even paypal you $5 for your effort.. However, as i mention, though i don't show in this sample excel file, i will have more sheets then two, it will be 3 or 4 actually. But the main content will be on sheet 1 and the other sheets will autofill/update from sheet 1 always. Sheets 2,3,4 will never talk to each other. Thanks again..

relational-sample.zip

[Søren Dyhr]

, I can't get my head around relational database concepts probably

Well no, it seems to go wrong somewhere, what you probably are missing is that relational linking usually are done on daft serial numbers, and no matter from which tables layout you wish to watch something from isn't it a copy, but a referenced value to prevent the synchronization issues you mentioned! Each piece of data has only one single physical location!!

I've tried to guess what you should be after, and made a template to give almost the same touch and feel as the spreadsheets discourse suggests.

--sd

telegix.zip

[BCpRIA8Smh]

Thank you very much for the feedback.. I had time to sit down and better understand what you did.. However I am slightly better now. Of course, on those new tables I now want to calculate things.. As you see, I have included my project as well as the sample you made me.. I added an extra field to your sample on the related-field table called MSRP (manufacture suggested retail price). In this, as you can see from my calculation, i take the price from main table and multiply it by 2.2. This kind of works on your sample and not at all on my project. On your project, the calculation does not happen till i don't enter some information into the extra_field box on the second tab. Then, when i press enter it suddenly does the calculation automatically and shows it correctly. It even recalculates when i change something on the main tab. Why does the calculation not appear automatically when i enter the values on the main tab? Then on my project, you notice on the second tab that the MSRP does not appear no matter I do, why is this? I must still have something done wrong somewhere.. I have attached the work in progress files to this msg.

telegix-changed.zip

[Søren Dyhr]

Whoa! Do not expect a "Done that, Been there, Got the T-shirt, worn it out and used it as a duster"

I had absolutely no calculations in my template, you added it to act on a related value in the mod of my template but in your own a field in the same table. You should make it absolutely clear to yourself where the field belongs, it can't be in both tables if it is the relational structure you aim at.

I do then look ever so slightly horrified at your use of postfixes as such in your fields - well they're what you could expect from a spreadsheet jockey who just have dipped his toes in the database sea. However does these need to broken out in their own tables as well ... I can only guess what the purpose is here, is it some kind of price matrix?

If you expect say Choose( in a combination with Case( is an efficient way to deal with price matrixes, am I about to disappoint you here. Such a construction is a giant on clay-feet when it comes to rendering. You should utilize a multi criteria relational structure to facilitate this and unstored calc's containing:

http://www.filemaker.com/help/FunctionsRef-316.html

I can't be absolutely sure here, is the mission-statement here just to have means to eyeball the best price from various suppliers? Or should the quantity or the measures single out one specific supplier as the optimal purchase?

--sd

[BCpRIA8Smh]

Thank you for being so frank.. Yes indeed I'm a spreadsheet jockey as you can see.. However i'm trying to learn.. I have again modified your sample with a lookupnext function to do the calculation.. I have tried it as stored or unstored calculation. However, the MSRP refuses to appear unless I enter some information on the related tab (and consequently the related table). After I enter something it does the calculation. How do I get it to automatically calculate and fill out the related MSRP field?

The purpose of my filemaker project is this. I have a main table with the information needed to supply my e-commerce website with updates, etc. I have my products listed on Amazon, PriceGrabber, etc. Those companies require some of the same fields (sku, company, etc). They also have their own fields which I don't use in my e-commerce website. This is why I would like to enter my information on the main tab and then go to the amazon tab and enter the amazon specific information. My website is in CDN dollars as well, while the amazon prices must be in USD. This is why I need to do some calculations, etc.. When I go to export, I will make a template for each type of CSV file I'm looking to create so that the amazon computer will accept them. I hope I am being clear.. I have included your modified sample again with the lookupnext function. Could you perhaps fix it so that it automatically updates when you enter information on the first tab. I did notice that if I say print, then it does the calculation and fills out the field on the printout. So, maybe I just need to run some kind of script to force it to update the relatedtab? Thank you again..

telegix.fp7.zip

[Søren Dyhr]

Alright then is it not the lookup next we need here - I just wondered why the postfixing with numbers occured, but instead a basis price on your own tab, but a price with a sometimes different margin shown in the various retailers tab, right???

It could be done this way - see template, but it isn't the way I would solve it. With the appearance of webobjects, can you let it sniff which tab you're on and then adjust the price accordingly to it, and then pluck the right factor via a calc.

But perhaps is the problem that there is no data in the selected pane until you have written something in the extra field which initiate the create the related record:

--sd

telegixSd2.zip

[BCpRIA8Smh]

But perhaps is the problem that there is no data in the selected pane until you have written something in the extra field which initiate the create the related record:

--sd

Ok, I think this is our problem. Your right, when I enter a record into the main table, the related tables don't get the extra record automatically, and consequently the MSRP isn't calculated like it should. So, how shall we proceed to fix this problem.. When I enter the price on the main record, how do we get it to create a new record on the amazon and pricegrapper tables?

[Søren Dyhr]

Alright the prices are fixed margins, so no matter which new product is chosen will the prices displayed change according to the selection, then are we back in the lookup scenario again.

Check this, although it could be done with some trickery in a tabbed layout, isn't it the most obvious to implement - we're here in reality talking about two different relations not just one. A many2one and a one2one!

Update - update, it should be obvious that deletion in the many part shouldn't delete records in the one part ... see the image! I apologize this severe error!

--sd

telegixSd3.zip