Data - Where

[lee.copper]

Still very new and I could be completely wrong with what I am doing.

I need to limit the information that is being returned so I need a where data statement. I have proved this by manually specifying one of the parameters but for the life of me I cannot figure out what the variable is.

So, I have two tables the first is my list of birds and each bird is given a unique id called ID. The second table contains weight which are logged on a daily basis, this table has a date, weight and the id of the bird.

With no WHERE specified the form works but it returns all the weights for all the birds. If I add the where statement and set it to = 1, my form then returns the value of bird 1. When I then open the form again as bird 2 it still gets the weight for bird 1 because that is whats specified in the WHERE statement. What I need to know to set the value of 1 to the variable that matches the bird being viewed.

I hope that all makes sense.

 

[felixkat]

I'm not entirely sure what values you are hoping to see.

I could be way off here but I don't believe you currently have any joins between the two tables and you are trying to do a WHERE statement to link them?

If this is the case then you can link your two tables in a couple of the different ways. It all depends on your long terms goals and how you may further expand the information you are entering.

The first option you can do is to create and database join element in your weight table. In the parameters for your database join element you want to point to the bird table, using ID, (the fabrik created one), for your value and the bird name \ ID, (you ID reference), as the label.

When you add a record into the weight table you can select which bird it is for. You can then add a 'filter' on the bird element in your weight table. This allows you to view all the weight entries for Bird X.

Another way of doing it is a repeat group. This way round you only have the bird table but you are creating a repeated entry for the weight.

Your long term goal depends on the best method to use. If you want to start adding additional information such as locations, type of bird feed, weather.... Then you separate tables will be the better method so you can normalise your data.

Sorry if I have got this completely wrong.

 

[lee.copper]

No your sort of right. I have in fact three tables. One for the bird, another for the species and the third for the weights. The weight will need to be a separate table because theres going to be a lot of them. The table already has a link as best as I can tell because I am joining it in the data tab, but I am very green to this application so could have it wrong.
If you would like to take a look I am happy for you to log in and take a look, a picture speaks a thousand words as they say.

 

[felixkat]

So you want to add an for weight but select the bird and the species and two different fields?

 

[lee.copper]

the bird is the main table, the species and weight are joins.

 

[felixkat]

Ahh okay, Sorry this is lengthy. There's different ways of doing these things which then depends on how you want to enter your data and how you want it represented.

Let me knock up a quick example so we can modify as required. I can then use this to create a tutorial so it's in everybody's interest.

 

[lee.copper]

Great, Love a tutorial which have got me this far.

 

[felixkat]

Okay, I'll set something up.

Any chance you can zip up and send me your three tables? You should be able to PM them to me if they are small and you don't want them public.