How do I get dynamic Choice field towork?

I’m trying to create a database that mimics one originally created in Access and subsequently migrated to FileMaker. I’m getting a bit desperate, as I’m now in the last five days of my trial. If I don’t solve my ‘big’ issue, I’ll be stuck with using my newly created Numbers spreadsheet, which works, although in a fairly klutzy fashion.

I’ve previously received advice on the forum before ( thanks to Fred and John Halls ) and am hoping that an answer (quick response; detailed; and, couched in terms that a six year old will understand ) again.

I’m currently revising my design, and am now thinking on one that ‘depends’ on Choice fields, and am trying to see if it works using a very small design.

Two tables.

M:N - ProviderCategory - two fields, Provider and Category - both Choice.

Payments -two fields of concern at the moment - Provider (Choice) and Category (dynamic Choice). Also a Link to ProviderCategory with a constraint on the Provider field.

I’ve entered the following text in the Dynamic values section of the Category field (Payments) and have told (in red, no less) that I’ve made a mistake (no surprise) and that ProviderCategory cannot be found.

ProviderCategory.Category where ProviderCategory.Provider = Payments.Provider

I’d really appreciate somebody telling me what I have to do to get this working. When I enter ‘Fred’ in the Provider field (Payments), I’d like to see ‘Burger’ and ‘Pizza’ in the Category field, as both ‘Burger’ and ‘Pizza’ are in the ProviderCategory field, and connected to ‘Fred’. I do no want to see ‘Broccoli’ as a choice, because there is no way that ‘Fred’ would ever be connected to ‘Broccoli’, certainly not in the ProviderCategory table.