New to Filemaker pro 7

[Mohammed]

Dear All,

I am still new to the world of filemaker. anyhow, i have here in my work , one excel sheet for our products, that we used to enter the price for this material .

I also, attached the excel sheet with this question.

anyhow, i create one dadtabase file , with this name 1.fp7 & it has many tables inside this database.

I made the tables as per the name of the product. for example, i am working with one product called ( C9 ), so i create one table called C9.

The fileds inside this table, is exactly as the one which is in excel sheet, i attaced.

The excel sheet have the followings -

1.Date

2.Low Price

3.High Price

4.Avg Price.

5-Week Change

6.Week Avg.

so, on everyday, i used to enter these data on excel sheet and the result will be calculated automatically.

Now, let us do for 2 days.

Day one, low price will be 20, High Price 30, Avg is 25, Week change is 0.

Day Two, low price will be 40, High Price 20, Avg is 30, Week change is ( Avg of today-Avergae of Yestrday), 30-25 will be 5.

Day 3, low price 60,High Price 20, Avg 40, Week change ( 40-30=10 ).

and so on...

Now the Problem for me, on Filemaker, is how to calculate the week change and how to let it read every day i put. ?

can you please help me.

I attached with this questions, 3 Pic as gif for my feilds inside this table which is C9, please see it and help me.

Regards,

Rola

[Fenton]

The first thing to understand about the difference between spreadsheet applications and FileMaker, which I'm going to put in my own words. A spreadsheet has a "location" on worksheet/screen, which the data is tied to. FileMaker data (fields) do not have a tie to a specific location on a particular location. Each belongs to a particular table, but not to any particular place on the layout/screen. There's (much) more to than that, but the basic concept is that a database is more "abstract" than a spreadsheet. It is more about logic than location.

You have several "cells" on your worksheet, in locations which you say are "days of a week." In FileMaker you would have a table for daily entries, a new record for each day. It would have the date, and your data entry for the day, a field for Low, and one for High.

Actually it sounds like these are from another table, from the multiple data for the day, which you could either calculate or script setting the Low and High from. But we'll ignore that for now.

So, your first calculation, for the daily average (let's call it "_cAvg") would just be: Average ( Low; High )

Week requires more. You need a way to tie all entries for a week together. In FileMaker we use a relationship to tie things together, in this case a self-relationship, which just means it's within the same table. A "week" is actually the Year (date field) and the WeekofYear (date field). The week does not need to be padded for the relationship, but I do it, so that it sorts correctly, if you ever need to do that, which is likely. Calculation, result Number.

Year (Date_) & Right ("0" & WeekOfYear (Date_); 2)

If you create a self-relationship (let's call it "self_week") on that you will have tied the entries for a week together. You could then use a calculation to average the entries.

Average ( self_week:_cAvg )

This is an weekly average of the daily average. It is pretty the "relational design" way of doing it.

The way you mentioned, progressively doing this one day at a time, Day2 -Day1, etc.. Well, that also could be done. You would likely want to do the whole week within only 1 record, with 7 separate fields for the days. Then you'd have to do the Day2 - Day1, etc., pretty much like you did the spreadsheet. But I don't really want to do it :-]

[Ocean West]

I didn't see any attachments with your post (zip them first to attach them)

From reading what you posted I wouldn't create a table for every Product.

I would have a product table then create a second table called product_changes to track your daily changes. form here you can create sub summary and or comparison reports.

[Mohammed]

Ok,

If you look to the Gif1, you will find that, i design the Database, based on the Product.

I mean Each Product, will be have a table. so the DATABASE will consist of many Products ( which is many Tables ).

do you think, the design is wrong ?

You will find an excel sheet with 2 gifs.

What do you want to say exactly ?

Do you want to say to create a table ( only one table ) called daily entry, and this table will have the entries for daily basis ?

If so, i have many Products, how can i do it?

Or , as another solution, make only one new table with this name ( daily entry ) , and it has feilds of ( low price, High Price, Avg Price, Week change ).

can you please be clear ?

Rola,

My E-mail is -

[email protected]

[Fenton]

You will have a table for Products, one record (row in Excel) per product. Each should have a unique ID. Then you would have a table (C9 as you show) for the for the entries. BUT, it will be only ONE table for all entries, and it will have the Product ID as a foreign key field.

It is somewhat difficult to explain relational structure to someone who as only used spreadsheets. Take a look at this simple example.

HighLowAverages.fp7.zip

[Mohammed Tantawi]

You are correct, the only thing which i need it now is compare a value between the current record and the previous one.

So, Depending on that i need you to give me your openion about the 3 questions i want to clear it from my mind -

Question 1:-

1- Do you recommend to change the structure of the database tables, and only do Five tables as one for Products , and other one for Daily Entries , Third one is for Marketing names, Forth one is for months , Fifth one is for the Year ? do you rocommend that ?

If so, here is the structure for each table with its feilds :-

1. Table 1, the name is ( Products ), have the following feilds:-

---------------------------------------------------------------------------

1.1 Product ID

1.2 Product Name

1.3 Product Internatinal Code.

==================================================================================

2. Table 2, the name is ( daily Entries ) , have the following feilds:-

-----------------------------------------------------------------------------

2.1 Date of Today.

2.2 Low Price.

2.3 High Price.

2.4 Average Price.

2.5 Week Change

2.6 Week Avg Price

====================================================================================

3. Table 3, the name is ( Marketing Names ) , have the following feilds:-

-----------------------------------------------------------------------------------------

3.1 Marketing ID.

3.2 Marketing Code.

3.3 Marketing Name.

3.4 Marketing Location.

====================================================================================

4. Table 4, the name is ( Months ) , have the following feilds:-

------------------------------------------------------------------------------

4.1 MOnth ID

4.2 Month Name.

===================================================================================

5. Table 5, the name is ( Years ) , have the following feilds:-

---------------------------------------------------------------------------

5.1 Year ID

5.2 Year No

====================================================================================

Do you recommend this structure ?

Please clear it for me.

====================================================================================

Second question is :-

---------------------------

2- i want really to understand one thing, because i am really still new to the Database world, what is the mistakes in my design which i did since i start ? can you please clear it for me ?

----------------------------------

Third question :-

3- Do you think i am correct, when i am doing design DB, i have to ask my self, what i need from this DB as , report ? i mean , i have to ask my self, how many reports i need , and which type of report i need ? and how the report looks like i need .

====================================================================================

Also,

WHEN I START PUT THE FEILDS, I GOT THIS ERROR MESSAGE, WHEN I SHIFT FROM LAYOUT MODE, TO , BROWSE MODE , IN FRONT OF EACH FEILD.

UNRELATED TABLE.

AND HERE IS THE PIC TO EXPLAIN TO YOU THAT.

PLEASE CLEAR IT FOR ME.

Also, i am still have the same Problem, I am getting an Error message when i am trying to add New Recorde , with this name ( Table is missing ) as per this Picture.

While i create the tables and i saved the data base even .

Can you please tell me what is going on ?

Waiting for your reply.. urgent ... on my 5 questions .

Rola

[Fenton]

Well, I don't know what "Marketing" is, so will ignore for now. Look at the file I uploaded. The main thing you are missing structurally is that the ProductID must be in the Daily entries, and that the two tables are tied together via the ProductID, on the Relationship Graph. You cannot ignore the relationship graph or its relational ties.

As far as Month and Year tables, I don't really see, at present, why they are needed. Everything to do with "date" depends on the Date field in the Daily entries. Reports will be done in Daily entries. Subsummary by week, month, year can all be produced there. A month is always year & month.

Your problem with the fields on the layouts is that you deleted the "table occurrence" (TO, large box on Relationship Graph which is tied to a particular table). Every layout is tied to one of these, deleting it causes what you see, disconnected fields. You need to first reassign the layout to the proper TO. Then reassign each field. Or, just create a new layout, and specify the fields, then delete those layouts; whichever is easier.

The structure I used in my example file is the simplest relational model. As I said, it may not be the fastest in operation.

[Mohammed]

thanks for your reply.

i tried to open the attachment in your attachment and i could not open.

i have here in my pc, file maker developer , 7

waiting for your reply.

BTW, can you please be very clear about your ex[plain. i could not understand very well. would you please be kind enough to repeat it again.

rola

[email protected]

[Fenton]

Is anyone else having problems with my zipped file? I see 10 downloads (one of them me). It works fine for me.

[mr_vodka]

Nope Fenton. Works for me.