Help on linking a table

[EKoster]

Hello everyone. First post so I hope it's in the right thread, if not I appologize smiley-smile.

 

I making a report against an online mysql database. This database contains a webstore. I need to join two tables. First lets describe the tables:

 

Table 1: Data (contains orders)

Table 2: UserInfo (contains order user information)

 

What I need to do is have two links:

1. data.order_id = userinfo.order_id where type = BT (billing adres)

2. data.order_id = userinfo.order_id where type = ST (shipping adres)

 

The combination to determine the shipping adres

if count(user.order_id) > 1 then link 2 else link 1

This for if the shipping adres is not entered seperatly, it's automatically the billing adres

 

Can I make this with a function or do I need to make views on database level?

[Databasic]

Hello EKoster

 

I think noone has replied because its very difficult to know what you want .

You have an Orders table and a User table ... and you want the address in the order table filled in by data from the user table - specifically the shipping address and if there is no shipping address in the User table you want the billiing address from the user table entered into the shipping address on the order table- is that the problem ?

[EKoster]

Hello I'll try to point it out using a easy situation, accounts and adreses.

 

Say you have the account table. Adresses are stored in the adress table. This for all adresses have the same fields. An account has an ID. The link between the two is the account.ID column.

 

This should still be easy to follow.

 

Now each account has 1 adres, the default one. I put that in my overview on left under BILLING. Then some accounts have a second adres. As I said they are all stored in the adres table. Only the TYPE column in the adres table will have a different entry. Say 1 for default and 2 for the other.

 

Wat I'ld like is this:

If an account has one adres, print it on the left.

If an account has two adreses put one on the left, one on the right.

 

As you see I left the last part out namely if there is one print it left and right. I think this should be possible.

[EKoster]

Oh yes,

 

If I'ld do it in a sql script looping through an import or so I'ld do (for each field so e.g. for shipping company):

 

variable = SELECT company FROM adres WHERE order_id = order_id and type = ST

 

Order ID is you link column. So I now its possible but how here

[Databasic]

OK I understand the problem - how I would approach it in FM is to create another TO of the address table and create a relationship using a global value AND the account ID . Go to the link below and download the sample file addressplayv1 - easier for me to do a sample then try to explain

 

 

http://www.mediafire.com/?zwzmduttfre

 

I don't know if this will solve your problem and I don't know if this is the best method but it may put you in the right direction Cheers

[EKoster]

Thanks. This is nearly there :-)

 

Now I have billing filled every time correct.

If there is no shipping it also correct for it uses billing

If there is shipping it doesn't fill the right one.

 

Cause: I can't make the global field your using to link it. The table are in a mysql database. I'll have to figure out how to use that in my temp table from filemaker.

 

perhaps I can put every thing there?

[Databasic]

Is this a one off or ongoing ? Is it possible to import the Sql address table into the FM temp table then add the global field, then use the temp table to join to the accounts table ?