0

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.

32 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    your formula: ProviderCategory.Category where ProviderCategory.Provider = Payments.Provider should look something like this:

     

    let curRec := this; <-- curRec can be any name you want
    select ProviderCategories where Provider = curRec.Provider

     

    1) since you want to reference the current record you are on, you need to set a variable to captures that info, which leads us to line 1. "this" is Ninox language to grab the data of the record you are on.

    2) you need to start with select so Ninox knows what you are trying to do.

    3) you only need to provide the table name after select

    4) there is no need to reference the table name again after the where so you just need the field name

    5) this is where you insert the variable to the current record and the field you want to use.

     

    I see that you are testing out a new design. I am just curious to know why you are moving away from the previous design where you create reference fields to the Provider and Categories tables.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Thank you for your quick response, Fred. I’m finally beginning to believe that I might actually be able (with considerable help) to operate in Ninox. I entered the code you gave me in the Payments.Category, and a similar code in a newly created Service field (with appropriate M:N table). The cascade feature works in both cases, At the moment, it displays the Choice index value as opposed to the text value - i.e. ‘3’ instead of “Burger’ for Fred’ The Provider field (normal Choice) works just fine.

    I decided to change my model because I really want to use list boxes wherever possible. It appears that the only format for doing this in Ninox is to use Choice fields. I can eliminate the requirement for Parent tables by ensuring that I only entering unique options when creating the Choice field. I’m now working with a model that has two M:N tables and the Payments table.

    Daily Rant begins.

    It also seems that the only way to create the Choice options is to enter them manually. I have about 120 Services, of which about 40 are ‘current’. I can handle entering these. What if I had 1,500 or 15,000.

    When I retired from the Army, I took university courses (mostly computer) for a couple of years and then talked my way into a MBA programme. My intentions was to get an extremely well paid position where I didn’t have to do very much I’m still waiting on that, but I did end up teaching Access and Excel in a business computing course at the university for a number of years.

    The MBA programme required that I take of couple of marketing courses, and, from what I remember, it makes to sense to tell prospective customers that Ninox will work well for them if they will enter, manually, their 15,000 options in the Choice field, and the manual doesn’t suggest that there’s any other way of doing it. In a similar vein, why is there is no mention of a way of creating a key, or unique, index, in the manual. This was certainly mentioned in most of my texts as being an essential component of relational database design, and as far as I’m aware, it still is an essential element. Also, while in the Army, we’d do a procedure, if that’s the correct term, that we called an Appreciation of the Situation (Brit equivalent of US Army Estimate). One thing we were taught to do was to ask (frequently) ‘So what?’ I find my self asking my self this question frequently when looking at Ninox. ‘I’m putting a Link to field in my table.’ ‘So what’. ‘I’ve created a link between two tables’. ‘So what’. ‘I’m putting an a.Provider = b.Provider in my table’. ‘So what?’. What do these things do fro me, impractical terms?

    I really don’t get the Zen approach that Ninox appears to be taking in providing information. I’ll admit to being a database Luddite.

    Daily rant ends.

    I think that my current model will meet my immediate needs, provided that the list box display problem mentioned in my first paragraph can be corrected. Minor issues can be corrected when ‘Ninox for Dummies’ and ‘Ninox NX Scripting for Dummies’ become available.

    In any case, thanks, once again, for your quick and helpful response.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Forgot to mention, my M:N table fields are also Choice fields. No real reason that they couldn't be text, if that makes the dyanamic Choice work better.

    • Sean
    • 2 yrs ago
    • Reported - view

    Maybe I'm missing something, but you should know that the source for a 'Choice (dynamic)' field can be a table. That is really the main point, I think, for the field to exist. So, that means you can import a CSV file into a table and use that as the source for the field, and therefore, not need to enter the choices manually.

     

    Screen Shot 2021-05-21 at 7.12.23 PM

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Thanks for your response. I didn't know that the source for a dynamic Choice field coud be a table - it certainly isn't mentioned in the manual. I see in the image you posted that you have 'select Fonts' in the dynamic values portion and 'Font' in the Dynamic value name area. Is one of these the source table name?

    As I noted in my rant, I'm quite a Luddite when it comes to working with Ninox. Any advice you can offer will be appreciated.

    • Sean
    • 2 yrs ago
    • Reported - view

    Yes, Fonts is the table name and Font is the field name that is displayed in the 'Choice (dynamic)' field.

     

    I'm afraid my frustration came out in my response. I don't have time at the moment to check the manual, but if what you say is true then that is one of sources of frustration.

    • Fred
    • 2 yrs ago
    • Reported - view

    Yes the Ninox manual is the worst, but we know that. They supposedly hired someone but we have yet to hear anything or see anything from them.

     

    I have joined Nixous', a Ninox partner, member program. They have tons of video and a better manual. You can see some of their videos on YouTube, but they have longer ones when you become a member. Not pushing them but they have helped me learning Ninox.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    I have a similar journey to you but mine started with Foxpro to FileMaker to Ninox. I have learned that to get to grips with FileMaker I had to forget about Foxpro and think FileMaker. Eventually the penny drops and I wondered why I ever doubted its ability to do what I wanted it to do. It's the same with Ninox. Forget FileMaker and quite quickly the penny drops. Your worries about unpopulated links are unfounded once you stop thinking about how Access or FileMaker would do things. Ninox gives you a powerful language to quickly overcome the unpopulated links, and some.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I feel like I’m living in interesting times, in the sense of the Chinese curse. I’m trying to create what I think is a very simple database, which I’ve outlined previously. I’ve now replaced the previous M:N tables ( Choice fields) with tables containing text fields. I entered the I following formula in the dynamic Choice ‘Category’ field in the Payments table, and, wonder of wonders, it worked, displaying those Categories that are related to the Provider selected in the ‘Provider’ field.

    So, I entered the following formula in the dynamic Choice ‘Category ’ field.

     
    let curRec := this;

    select ProvCat where Provider = text(curRec.Provider)

    It worked!

    I then entered the filling formula in dynamic Choice ‘Service’ field.

    let curRec := this;

    select CatSvc where Category = text(curRec.Category)

    It didn’t work, and I have no idea why. I’m coming to the end of my trial period, and I suspect that I’m going to have to accept the fact that Nino is not a viable option for me.

     Any advice you can offer will be appreciated.

    • Fred
    • 2 yrs ago
    • Reported - view

    We would need some screenshots of the CatSvc table fields and the table fields that curRec refers to.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Hi, Fred. I see than I'm now classified as a ninja - dertainlyu not like any ninja I've ever heard of. I think you suggexted the one field, two links to M:N table. I like the structure - haave no idea how to use it.

    Anyway, my CatSvc table (JPEG - should show up)

     

    Somethingwrongwith aspect ratio. CatSvc table below.

    A_CatSvc PNG

    Hope this works.

    • Fred
    • 2 yrs ago
    • Reported - view

    Yea, the pics worked!

     

    Now can I get some pics of the edit fields windows? 

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    THink these are what you're looking for. By the way, there are no constraints in the Link to fields.

    A_Category Edit

    A)Service Edit

    • Fred
    • 2 yrs ago
    • Reported - view

    So when you are in the Baker record of your payment table, when you click on Services nothing shows up? I only ask about a baker because it is the only record with a category selected. 

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Exactly. When I enter the Payments.Category field, those Category values in ProvCat where Provider is ‘Baker’ are displayed. After selecting ‘21Cat’ in Payments.Category, I should see the three Services associated with ‘21Cat’ in CatSvc. I see nothing.

    I’m not aware of any spelling mistakes, or any errors in crating the formula. The only difference I can see is the Payments.Provider is a Choice field, whereas Payments.Category is a dynamic Choice field.

    • Fred
    • 2 yrs ago
    • Reported - view

    Can you do a screenshot of the following:

    1) open CatSvc table, click on the gear icon and select Edit Fields

    2) take a screenshot of the table name and all the field names

    • Fred
    • 2 yrs ago
    • Reported - view

    something like:

    Screen Shot 2021-05-17 at 08.52.44

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    OK

    A_CatSvc Edit

    • Fred
    • 2 yrs ago
    • Reported - view

    I'm guessing that Payments.Provider field is a NOT a dynamic choice field.

     

    It is a bit tricky using dynamic choice fields and then using that data to do searches. Ninox has no info and people are just figuring it out.

     

    You mention that you didn't understand how to use the M:N tables. You said that you were able to setup your Payments table with a reference field to Provider, Categories, and Services and you got it to work where when you selected a Provider it gave you a subset of Categories which gave you a subset of Services. Isn't that how you want it to work? I'm not sure why you switched to dynamic choice fields.

     

    The main reason to use reference fields is that now all the data is linked so if you choose a Categories you can find all providers, services, and payments linked to that category or any combination you want. With choice fields there is no hard link between anything. You will always have to reference the Payments table to find any relationiship between the three tables. Or to find a link a link to providers and services you have to do a search of payments.

     

    I hope that makes some sense.

     

    Like you said earlier, it will take some time to learn the Ninox way. I am using the MacOS app so a one time fee of $35 was very reasonable for me. For my personal db I'm not sure a monthy fee would be reasonable.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Payments is Choice, not dynamic choice.

    You described how I wanted it to work. I never got it to work, which is why I thought that switching to 'dynamic Choice' was a good idea - I liked the word 'dynamic' before I tried to implement it in Ninox. My every day interactions with the databse will always be sequential - Provider to Category to Service.

    I think that the one time fee is reasonable, and I'm quite prepared to fork out in order to 'play' with Ninox. My current problem is that I'm trying to implement  a 'work' (persoanl, but still work) solution. At the moment, I'm looking at using my klutzy Numbers workbook, becuase it doesn't look that I'm going to find the solution right now in Niinox.

    Anyway, thanks for looking at it.

    • Fred
    • 2 yrs ago
    • Reported - view

    Did you setup your DB like in the thread below?

     

    https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/can-i-replicate-access-key-fields-indexes-enforced-relationship-integrity-and-data-entry-options-in-ninox-6096eaa311e34246258d4c35?

     

    Also you said on May 21 at 9:33PM in this thread that you got the cascade feature to work. So I'm confused as to what you are trying to do?

     

    But if you are done talking about this that is ok too.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I did, but I thought that I had everything solved when I got one cascade to work, I hadn't tried the second. Mayabe I should give it another try. I've also finally got someone from Ninox who seems to be prepared to have a look at the dynamic Choice problem, so will see where that goes. Any way, my free trial has expired , so will probably be off or a few days.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I've looked at this a number of times,and the only plausible answer I can come with to explain why the second formaula doesn't produce the desired result is that it's not picking p the value in Payments.Category. Anybody have any thoughts on this.

    I said, acouple of posts ago, that 'Payments' was a Choice field. I meant 'Provider'.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    I had a 15 minute session with Maria Luisa Pasini, in which she solved my most significant problem - the others I can live with. So, ’Grazie Maria’ and  ‘Obrigada Maria’!

    There was nothing wrrong with my formula in Service - it just didn't work (bug).

    She gave me the following formula.

    let t := number(Category);

    let c := first(select ProvCat where Id = t).Category;

    select CatSvc where Category = c

    So,I'll keep on trying. I agree App Store version is the best bet.

    • jmaci1987
    • 2 yrs ago
    • Reported - view

    Nothing wrong except fro me spelling 'wrong' as 'wrrong'!

Content aside

  • 2 yrs agoLast active
  • 32Replies
  • 3176Views