0

Dynamic multiple choice filter that works only on Server

Filter Order Location Copy.ninox

Hi Ninox Brain Trust,

Attached will be found a small database that has a filter for a dMC (Customer.Location) that only works on the Server.

Please look to see if there is an alternative formula that may work for the reference field (Orders.Location) .

As a workaround you will also find a dMC (Orders.Customer Locations) field that is also pointing at the Locations table. It appears to be working offline in the Mac App. However, I've been unsuccessful filtering with the current formula.

thanks for looking!

12 replies

null
    • Fred
    • 9 mths ago
    • Reported - view

    Hi Sam -

    Once you start working with multiple choice fields (regular or dynamic) then you can't use the equal sign so easily.

    Luckily now you can use the contains() function to filter on arrays, so maybe try:

    let t := this;
select Locations where contains(numbers(t.Customer.Location), number(Id))

    or you can do:

    for loop1 in numbers(Customer.Location) do
    record(Locations,loop1)
end

    Since a dynamic field is based on a table we can get record Id so we can use the record() command in a for loop to get an array of the specific records that were selected. It could be faster depending on how large the base table for the dynamic field gets.

    For the Contraints is would look very similar:

    contains(numbers(a.Customer.Location), number(b))
      • Sam.1
      • 9 mths ago
      • Reported - view

       Thank you very very much for the perfect formulas and detailed description!!

      thanks again for looking!

      • Fred
      • 9 mths ago
      • Reported - view

       glad it worked. Please mark the post answered when you get a chance. 

      • Sam.1
      • 9 mths ago
      • Reported - view

       Before I mark it answered, I realized that the workflow may be improved by a trigger after choosing a customer from the reference field. Since, there is only one location, it would be nice to have it auto populate the Location(s). That way if there are 2 or rarely 3, a choice could then be made.

      I've tried several combinations of code and they all have an error unless I put an = sign .

    • Fred
    • 9 mths ago
    • Reported - view
     said:
    Since, there is only one location, it would be nice to have it auto populate the Location(s). That way if there are 2 or rarely 3, a choice could then be made.
    I've tried several combinations of code and they all have an error unless I put an = sign .

    So now not only do you want the dynamic choice field to show only related records, but you want the choices to be selected. Is that correct?

    If I choose Customer "Dick", "Here" and "There" show up for Customer Locations as possible choices. 

    You want those two choices to also be selected as well?

    Looking at your sample DB, the Customer Location field in the Orders table is a dynamic choice field, so you can never select more than 1 choice. But some customer records can have more than 1 Location selected. How do you know which record to select?

      • Sam.1
      • 9 mths ago
      • Reported - view

       I didn't consider that. Good Point.

      So, if we change the dC to a dMC--is it then possible to deselect one of the two choices that are populated by the trigger? If so, I think that would make more sense than also having to choose when there is usually only one location.

      Is that possible? What are your thoughts?

      • Fred
      • 9 mths ago
      • Reported - view
       said:
      is it then possible to deselect one of the two choices that are populated by the trigger?

      Yes, you can deselect a choice.

      The trigger could look something like:

      let selectedChoices := numbers(Customer.Location);
dMC := selectedChoices

      dMC is a new dynamic multi choice field that is setup like Customer Location.

       said:
      If so, I think that would make more sense than also having to choose when there is usually only one location.

       The one issue with using choice fields in the Orders table is that the field can be easily changed and then you have lost historical data.

      You may want to think about creating a Page where you create your orders, then on your orders page you would have a fields that store location in static fields, in fact almost all fields should be static, except for customer and for formula fields that only reference fields in the Orders table.

      Why static fields? I would think you want your Orders table to be historically accurate where you can return to an order and see what was ordered, how much it was for, and where it went. If you created a reference field to Location, what if the customer changes their location? Now you want to delete the old location data, but can't because then it would delete all information in your Orders table.

      If the location data was static in Order, you can delete/change the Location data and have the information available for future orders, but your past Orders still show the proper location data of the time of the order.

      • Sam.1
      • 9 mths ago
      • Reported - view

       thanks again for all the info!

      What if the fields are not writable after the invoice is created?

      Would that satisfy the issue of lost historical data?

      • Fred
      • 9 mths ago
      • Reported - view

      It doesn't deal with the dynamic fields.

      The Customer Location is based on the relationship of the customer record to the location table. If a customer changes their location and you remove the relationship then it will not show up anymore in the Orders record. You have just modified your Order history without touching a record.

      You will run into this issue if you use a formula field to get your order items price. Then if you change the price of an item it will change the price for all records that use that relationship.

      This could also happen if your customer changes their name, so if you change it the Customer table then all Orders related to it will also change. That might not be a big issue, but you can see the power of relationships.

      With the creation of an Place Order Page, all of your data entry can be quick and easy.

      • Fred
      • 9 mths ago
      • Reported - view

      In the end only you know what is best for your DB. My comments are just food for thought. 

      • Sam.1
      • 9 mths ago
      • Reported - view

       has the option to mark the topic answered been removed?

      It does not appear on any app or device for me

      • Fred
      • 9 mths ago
      • Reported - view

      That is weird. I looked at one of my posts and I, too, have no option to mark the post answered.

      I've seen other post get marked so I don't know what is up.

      Maybe time to send Ninox and email.

Login to reply

Content aside

  • 9 mths agoLast active
  • 12Replies
  • 83Views
  • 2 Following