Search records in join table and choose field data

[RobertM]

Hello Everyone,

I have an issue I'm trying to solve in a function. I have a join table that has 5 country ID's from a foreign table (UK, France, Germany, Spain & Italy), I want to look at the one that is France and read the value of certain fields in the record associated to France. How do I get Filemaker to cycle thru the country field until is finds France ID, then read the field value I request?

 

So for clarity there is a join table that has 5 country ID's. And the thing is in that table that country can be there more than once, but only once in there for a certain product with a unique price for the product in that country.

Example

Join Table CountryToysPrice

Toy a

• France €13,-
• Germany €16
• Italy €10
• Spain €19
• UK £ 14

Toy B

• France €11,-
• Germany €17
• Italy €18
• Spain €13
• UK £ 10

I need to be able to find toy B and read the value for France and use that value in a calculation by either putting it in a variable let function or some other way of using the field value in the calculation.

I need to be able to choose the toy ID or Toy name then choose country, so it has to be reusable for the other countries.

 

Kind regards,
Robert

[Ocean West]

ExecuteSQL ( "Select Price from CountryToysPrice WHERE ToyID = ? and country = ?" ;  ""  ; "" ; Table::GlobalToySelect ; Table::GlobalCountrySelect ) 

ExecuteSQL ( "Select Price from CountryToysPrice WHERE ToyName = ? and country = ?" ;  ""  ; "" ; Table::GlobalToySelect ; Table::GlobalCountrySelect ) 

You would need to have A global field that has values to select from.

 

[comment]

3 hours ago, RobertM said:

I need to be able to choose the toy ID or Toy name then choose country, so it has to be reusable for the other countries.

In which context do you need this? If you select the country in a global field defined in the Toys table, then you can use a relationship or a calculation like this one to show the price of every toy in the selected country.

[RobertM]

Hello Ocean West,

 

I am trying ExecuteSQL, but running into errors. I am getting this error in monitor: There is an error in the syntax of the query.

I've going at it for about an hour now, but I still don't see it.

 

Here is the code:

    $FranceSellPrice = ExecuteSQL ( " SELECT SellPrice
                                    FROM Joi_AsinMarketplacePrice
                                    WHERE Joi_AsinMarketplacePrice::_MarketplaceIDfk = 2
                                   "
                                    ; "" ; "" ) ;

[RobertM]

On 2/19/2019 at 10:00 PM, comment said:

In which context do you need this? If you select the country in a global field defined in the Toys table, then you can use a relationship or a calculation like this one to show the price of every toy in the selected country.

Hello Comment,

 

What you explained there went over my head. I haven't used global variables as of yet.

[Fitch]

9 minutes ago, RobertM said:

    $FranceSellPrice = ExecuteSQL ( " SELECT SellPrice

                                    FROM Joi_AsinMarketplacePrice

                                    WHERE Joi_AsinMarketplacePrice::_MarketplaceIDfk = 2
                                   "
                                    ; "" ; "" ) ;

 

Remove the table name from the WHERE clause:

WHERE _MarketplaceIDfk = 2

Except SQL doesn't like starting field names with an underscore so you have to use quotes and when you use quotes inside a string you have to escape them, so:

WHERE \"_MarketplaceIDfk\" = 2

[RobertM]

27 minutes ago, Fitch said:

 

Remove the table name from the WHERE clause:

WHERE _MarketplaceIDfk = 2

Except SQL doesn't like starting field names with an underscore so you have to use quotes and when you use quotes inside a string you have to escape them, so:

WHERE \"_MarketplaceIDfk\" = 2

Hello Fitch,

When I remove the table name I get the error: <Table Missing>

 

[comment]

1 hour ago, RobertM said:

What you explained there went over my head. 

I will go into more detail after you answer my question. 

 

1 hour ago, RobertM said:

I haven't used global variables as of yet.

I spoke about a global field, not variables.

[RobertM]

8 minutes ago, comment said:

I will go into more detail after you answer my question. 

 

I spoke about a global field, not variables.

Hello Comment,

I have never used Global fields either.

When you ask: In which context do you need this?

Could you clarify what you mean by this?

I'm going to give an answer based on what I think you are asking me.

There is a main table named Products and all other table are related to that table in some way with joins. The idea is on the Products layout that I can show the price of the product from each country based input fileds on the layout, that are them selves from other tables.

Products - Countries

Products -

• Dimension ( Length, Height, Width)
• Weight

 

Products - Brand type

Products - Suppliers

 

So everything comes from the context of the product.

But here is the one area that it has deviated for the first time. With this calculation, I am putting the context from the suppliers table, so I want to see that toy from that supplier with the country price attached to it. So each toy the supplier has, I can see how much it is for sale in each country.

 

Toy name               France     Germany     UK     Italy     Spain

Disney prince               40%           72%      25%     18%       74%

 

Each country will be showing a calculation of the ROI. What is now need is to be able to put a calculation in each of those countries whilst the calculation chooses the correct Toy, country, supplier combination.

 

Hope this brings more clarity. I have everything else created and running, but this last part with setting the correct country with supplier and toys is the last thing, for now.

 

Thanks in advance,
Robert

 

[comment]

I am afraid I got lost trying to understand all that.

See if the attached can help get you started.

PriceByCountry.fmp12

[Fitch]

On 2/22/2019 at 1:27 PM, RobertM said:

When I remove the table name I get the error: <Table Missing>

Post the whole calculation, maybe you didn't escape the quotes or something.

[RobertM]

On 2/26/2019 at 5:43 PM, Fitch said:

Post the whole calculation, maybe you didn't escape the quotes or something.

Hello Fitch,

 

By the time I read this message I had already re-written the SQL query and tried another query approach.

What I would like to ask. How do I view the table / records being generated? I am building a inner join between three tables, but I would like to first review  what is being generated.

What would be ideal is to see the table in the date viewer and also on a layout for testing.

 

Thank you in advance,
Robert

On 2/22/2019 at 11:30 PM, comment said:

I am afraid I got lost trying to understand all that.

See if the attached can help get you started.

PriceByCountry.fmp12 148 kB · 2 downloads

Hello Comment,

 

Thank you for taking the time to create that filemaker file.

 

Kind regards,
Robert

[Fitch]

14 hours ago, RobertM said:

What I would like to ask. How do I view the table / records being generated? I am building a inner join between three tables, but I would like to first review  what is being generated.

What would be ideal is to see the table in the date viewer and also on a layout for testing.

No tables or records are generated by a SQL query. It just returns a block of text. This can happen anywhere you can invoke the calculation engine, but it's generally best to use ExecuteSQL only in scripts, typically in a Set Variable or Set Field step. You can then either see it in the Data Viewer, or put that field or global variable on a layout.