Structural Problem - How to choose multiple items to add to an Invoice?

Kent_Signorini
updated 2 yrs ago
19replies

I have Appointments. Each Appointment has multiple Invoice Items (since Appointments are only ever invoiced once, they double as the invoice). Available Items to add as Invoice Items come from a master Items table.

In my old system, I have a button to add Items as Invoice Items, but you have to create the Invoice Items one-at-a-time. In this database, I want to make it even easier for the user and allow them to pick multiple Items to add as Invoice Items all at once.

It appears that the popup for selecting records to link up only allows you to pick one at a time, which means Adding an existing record for each Invoice Item. Hm.

I was thinking I could do something like this, but maybe someone has a better solution:

1. Each Appointment has both an Invoice Items table and an Add Items table (hidden).

2. The Add Items table is hidden and will only ever have one record in it--the temporary Add Items record. When the user hits an Add Invoice Items button, they are presented the Add Items table's form.

3. On the Add Items form, there is a Dynamic Multiple Choice field that links to all Items in the master Items table. There aren't too many so a long list of checkboxes is acceptable.

4. When the user picks the Items they want to add to the Invoice using the checkboxes and then closes the Add Items form, there is code that creates an Invoice Item to the Appointment for each checked off Item from the Add Items form.

5. Once the Invoice Items have been added to the (visible) Invoice Items subtable, the user can add quantities etc. to each line item.

6. Somewhere, maybe on closing the Appointment form (tab) there is code to delete the temporary Add Items record so it isn't a useless hanger-on to the Appointment record.

I'm sure something like this will work, but it does seem convoluted. Perhaps there is an easier way?

- Ninox partner
- RoSoft_Steven.1
- 2 yrs ago
- Reported - view
Maybe this can help you?

https://forum.ninox.com/t/g9hrzlf/filling-lines-of-ordersarticles-with-dynamic-multiple-choice-fields-and-cascading-dynamic-choice-fields
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  RoSoft_Steven Hm. Says to find it in the EN Team Webinar and it's called Cascading DC Fields. Thing is, I have no idea how to find that on these forums. Searching just brings up more discussion about it. Where do I find the actual database to download?
- Fred
- 2 yrs ago
- Reported - view
Instead of working out of the Appointment table, use a dashboard. Which is just another table but allows you to control things better.

You would then create a reference field to Appointments so users can pick the appropriate appointment. Then you can either:

show a view element that show any invoice items

and/or

show a dynamic choice field that allows the user to select invoice items

When you show the dynamic choice than you can follow this post for that code. Scroll down and find the reply with the TrackdMCJ3.ninox attachment. You can allow it to auto update or put the code in a button.

The only thing that would need to be done is to update the dynamic choice with any currently selected invoice items. I'll see what I can do in the next day or so.
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred Aha. Thanks! I'll definitely look through that code.
  
  I actually want to avoid doing it in a dashboard if I can. I know I could add more functionality that way, but I want to decrease the number of clicks my user already has and simplify entry.
  
  One of the things that really drew me to Ninox was the beauty of the slide-over forms on each table. (And I like that they can see the table.)
  
  But this lets me think maybe I could do a similar thing as a "Add Items" tab on the form containing just the Multiple Choice Items field and have it auto-update the Invoice Items from there. That would eliminate a middle table/form. I'll see how it's done in the downloaded database first.
- Fred
- 2 yrs ago
- Reported - view
Here is what it could look like.

I would say it could be easier for user with a dashboard as they don't have to see the whole table.

I used Jacques TUR code in the button so you can add all selections. The beauty of his code is that it only adds new items and ignores selections that are already there.

kent.ninox
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred Awesome! Thanks!
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred I just opened it up. Looks like this will really help me out.
  
  By the way, is there any way to further filter the items shown on the Multiple Choice? I tried "select items where..." (assuming there are other fields to categorize the items) but it doesn't seem to like that.
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Kent Signorini How would you handle a Synchronize rather than an Add? (Including leaving the adjusted Quantities in the table for items.)
  
  Do you just need to do the whole iteration in both directions?
  
  Thanks.
- Fred
- 2 yrs ago
- Reported - view
Kent Signorini said:
By the way, is there any way to further filter the items shown on the Multiple Choice? I tried "select items where..." (assuming there are other fields to categorize the items) but it doesn't seem to like that.

there is only one field in Items in my sample, so you can't do a filter. If you add a yes/no field and make it true for a few items then you can add:

select Items[yes/no]

and it will filter.
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred Yeah, looks like the key is using [ ] rather than the where clause.
- Fred
- 2 yrs ago
- Reported - view
Kent Signorini said:
Fred Yeah, looks like the key is using [ ] rather than the where clause.

this works for me too:

select Items where 'In Stock'

I just stay away from use "where" since you can only use it with selects.
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred Yeesh. When I first tried "where" there it wasn't working. Now it does. I'll be it was a save/cache problem. These seem to happen a bit with the web client.
- Fred
- 2 yrs ago
- Reported - view
Kent Signorini said:
Kent Signorini How would you handle a Synchronize rather than an Add? (Including leaving the adjusted Quantities in the table for items.)
Do you just need to do the whole iteration in both directions?

I like how you asked yourself the question.

I was thinking about that that, but I felt like it would make it too easy to delete items. I would think you would never want to make it too easy.

It is a good experiment so I’ll post an update later.

I’m not sure what you mean by “leaving the adjusted Quantities”.

If you remove an item then the record is deleted so is the data for Quantity.
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred
  
  lol
- Fred
- 2 yrs ago
- Reported - view
Ok, here is the updated DB.

It was an interesting thought experiment.

There is a new button called Modify Invoice and a new Yes/No field called modify.

I also created a new dynamic multi choice (dMC) field (InvoiceItems) to run this experiment as well as a new button to make the changes (called Set Changes).

I had to add the button because I couldn't figure out a way to set the dMC through the field itself. So the Modify Invoice button does that for me. Which is where the modify field comes in. I use that to check if I need to show fields or not.

Thankfully, Ninox allows to setting of dMC and MC fields through arrays.

I've also updated the formula in the Set Changes button to take into account the removal of choices.

kent2.ninox
- Kent_Signorini
  
  2 yrs ago
  
  Reported - view
  Fred
  
  I'm getting really close to having this complete. Thanks for the help.
  
  One thing I'm struggling with in my own syncing implementation is I'd really like to generate an array of IDs for Items NOT checked off in the Dynamic Multiple Choice field. I know
  
  := numbers()
  
  will give me the IDs of the checked-off ones, but I need the unchecked ones to perform my reverse sync. (Rather than keeping a second table to track changes.)

Fred
2 yrs ago
Reported - view

Kent Signorini said:
One thing I'm struggling with in my own syncing implementation is I'd really like to generate an array of IDs for Items NOT checked off in the Dynamic Multiple Choice field. I know
:= numbers()will give me the IDs of the checked-off ones, but I need the unchecked ones to perform my reverse sync. (Rather than keeping a second table to track changes.)

Here is a good post to give you more info about comparing arrays.

You can try this:

let selItemsArray := numbers('Select Invoice Items');
let allItemsArray := (select Items);
let x := allItemsArray[(var t := this;
            count(selItemsArray[= t])) = 0];
concat(x)

So the first two lines are pretty obvious.

Line 3 & 4 is the code from the link above where we compare the two arrays. It basically says take each record in allItemsArray and count the number of times it matches a record in selItemsArray and only keep the ones that have a count of 0. Which will exclude the items selected in the dMC.

Line 5 is needed because when you do a select Ninox does not show the data of record Ids unless you put in a concat. The concat is for display purposes only. If you where doing a dMC you can remove the "let x :=" part and Ninox will display the appropriate records.

Then it got me thinking that you can modify the dMC to only show items that haven't already been selected. So I've modified the DB to show that. There is always more than 1 way to accomplish the same goal.

kent3.ninox

Kent_Signorini
2 yrs ago
Reported - view

Fred

Alright!

So I didn't use the array subtraction as I had hoped (I had already begun iterating) but I got everything to work perfectly. Thank you so much for your help (and the others). I thought I would share my final code and some screenshots here for anyone who finds this at a later time and has a similar challenge.

I have 2 tabs on my form: Appointment (where the bulk of the Appointment detail is, including the Invoice Items table) and Services (where there are 4 lists of available service Items--each list is a Dynamic Multiple Choice filtered by item Category).

I have code on both the Trigger before view and Trigger after hide functions of the Services tab.

When the user flips over TO the Services tab, the code goes through the Invoice Items and determines which checkboxes on the Services tab need to be checked. If an Invoice Item has been deleted since the last time the Services were checked off, that checkmark is removed from the chosen Services.

When the user flips AWAY from the Services tab (eg. back to the Appointment tab) the code goes through the checkbox Items on the Services tab and adds new Invoice Items if they don't already exist or deletes Invoice Items if the checkmark for that service Item has been removed.

Quantities for Invoice Items remain if the user changes them. (My very first try at this simply emptied the Invoice Items table on each swap away from the Services tab which would delete any changed quantities.)

This is certainly not super efficient code as there is a lot of iteration. I will most likely utilize the array subtraction you helped me with above at a later time to tighten this all up. I'm sure there are other optimizations that can be done, as well. I did use do as server for the whole ball of wax as it seems to speed the whole thing up and there isn't any user interaction through alerts or dialogs.

"SERVICES TAB - TRIGGER BEFORE VIEW";
do as server
    "Synchronize the Services tab to the Invoice Items";
    let invoiceItemsGroomingPackagesArray := 'Invoice Items'[text(Items.'* Category') = "Grooming Package"];
    let invoiceItemsGroomingPackagesIDs := for groomingPackageInvoiceItem in invoiceItemsGroomingPackagesArray do
            record(Items,groomingPackageInvoiceItem.Items)
        end;
    Packages := invoiceItemsGroomingPackagesIDs;
    let invoiceItemsAddonsArray := 'Invoice Items'[text(Items.'* Category') = "Add-on"];
    let invoiceItemsAddonsIDs := for addonInvoiceItem in invoiceItemsAddonsArray do
            record(Items,addonInvoiceItem.Items)
        end;
    'Add-ons' := invoiceItemsAddonsIDs;
    let invoiceItemsALaCarteArray := 'Invoice Items'[text(Items.'* Category') = "À la Carte"];
    let invoiceItemsALaCarteIDs := for aLaCarteInvoiceItem in invoiceItemsALaCarteArray do
            record(Items,aLaCarteInvoiceItem.Items)
        end;
    'À la Carte' := invoiceItemsALaCarteIDs;
    let invoiceItemsDiscountArray := 'Invoice Items'[text(Items.'* Category') = "Discount"];
    let invoiceItemsDiscountIDs := for discountInvoiceItem in invoiceItemsDiscountArray do
            record(Items,discountInvoiceItem.Items)
        end;
    Discounts := invoiceItemsDiscountIDs
end

"SERVICES TAB - TRIGGER AFTER HIDE";
do as server
    "Synchronize the Invoice Items to the Services tab";
    let t := this;
    "Get all the selected Items into separate arrays";
    let selectedPackagesArray := for itemID in numbers(Packages) do
            record(Items,itemID)
        end;
    let selectedAddonsArray := for itemID in numbers('Add-ons') do
            record(Items,itemID)
        end;
    let selectedAlaCarteArray := for itemID in numbers('À la Carte') do
            record(Items,itemID)
        end;
    let selectedDiscountsArray := for itemID in numbers(Discounts) do
            record(Items,itemID)
        end;
    "Create Invoice Items for Items that ARE checked off in the Services tab";
    for packageItem in selectedPackagesArray do
        if count(t.'Invoice Items'[Items = packageItem]) = 0 then
            let ii := (create 'Invoice Items');
            ii.(
                Items := packageItem;
                Quantity := 1;
                Appointments := t
            )
        end
    end;
    for addOnItem in selectedAddonsArray do
        if count(t.'Invoice Items'[Items = addOnItem]) = 0 then
            let ii := (create 'Invoice Items');
            ii.(
                Items := addOnItem;
                Quantity := 1;
                Appointments := t
            )
        end
    end;
    for aLaCarteItem in selectedAlaCarteArray do
        if count(t.'Invoice Items'[Items = aLaCarteItem]) = 0 then
            let ii := (create 'Invoice Items');
            ii.(
                Items := aLaCarteItem;
                Quantity := 1;
                Appointments := t
            )
        end
    end;
    for discountItem in selectedDiscountsArray do
        if count(t.'Invoice Items'[Items = discountItem]) = 0 then
            let ii := (create 'Invoice Items');
            ii.(
                Items := discountItem;
                Quantity := 1;
                Appointments := t
            )
        end
    end;
    "Remove Invoice Items for Items that are NOT checked off in Services tab";
    for invoiceItem in select 'Invoice Items' do
        let isCheckedOff := false;
        for id in selectedPackagesArray do
            if invoiceItem.Items = id then
                isCheckedOff := true
            end
        end;
        for id in selectedAddonsArray do
            if invoiceItem.Items = id then
                isCheckedOff := true
            end
        end;
        for id in selectedAlaCarteArray do
            if invoiceItem.Items = id then
                isCheckedOff := true
            end
        end;
        for id in selectedDiscountsArray do
            if invoiceItem.Items = id then
                isCheckedOff := true
            end
        end;
        if isCheckedOff = false then delete invoiceItem end
    end
end