Structural Problem - How to choose multiple items to add to an Invoice?
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?
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
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.
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 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:
and it will filter.
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 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.
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.
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.