Consultation billing DB

[GLS]

Hello,
I am pretty new to Tapforms, coming from extensive use of MS Access on PC, some SQL, and minor Bento and Filemaker experience on Mac.

I am going around in circles without finding a way to solve a problem in a relational database that I am designing for billing consultations.

Here is the basic design:
One form (Clients) contains the client information.
This is linked to a Form (Consultations) in a 1-many fashion as a client can have multiple consultations, and this works perfectly.
I created a form (Invoice) to send out the bills. It links 1 to many to the Consultations form and has a linked table for the consultations records.

The Clients form links 1-many to Invoice as a client can have multiple invoices.

I would like to generate invoices for a client for consultations that are not yet billed (Billed field unchecked in the consultation record).

Currently in the Client form I can add an invoice, but the linked Consultations table in the Invoice form stays empty (I can however manually add the records).

Is there a way to automatically displaying the records of unbilled consultations for the client in the Invoice consultation table?

Ideally it would be grand to update to “checked” the billed consultation for the client upon finishing the invoice…

Any help would be greatly appreciated!

Thank you,

GLS

[Sam Moffatt]

I’d probably handle this with a combination of a saved search and scripting.

Saved search is the easy part, create a checkbox for “invoiced” that I think you’re already hinting at and if that isn’t checked then it’s an available consultation. You could also use a script field for this which might make more sense in the long run too but a check box is nice and easy to get to in the short term. For the purposes of the script, I’m going to call this the “Consultations” form and the saved search is called “Uninvoiced Consultations”.

The next part is likely a form script that is on your invoices form. I’d expect the workflow is that you create a new invoice, select the client you’re interested in from the link from form field and then run the script. The script looks something like this:

function Import_Consultations() {
  let consultations = document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations");
  let invoiceClient = record.getFieldValue('fld-invoice_client_fieldId');
  for (consultation of consultations) {
    if (invoiceClient.getId() == consultation.getFieldValue('fld-consultation_client_fieldId').getId()) {
      record.addRecordToField('fld-invoice_to_consultation_fieldId', consultation);
      consultation.setFieldValue('fld-consultation_invoiced', true);
    }
  }
  document.saveAllChanges();
}

Import_Consultations();

It’s a bit rough and untested but essentially the idea is that you’re using the saved search to find candidate records that haven’t been invoiced, that’s our first line of the function.

The list of uninvoiced consultations is going to include all of your clients because it’s a simple saved search (for ease of use), so we need to make sure we only get the client for the invoice. The second line in the function is getting the client record from the invoice record we have selected (remember this is a form script in the invoice form, so we’ll have an invoice record selected generally). The fld-invoice_client_fieldId is the field ID of the “Link from Form” field that you will have in your invoice form. If that field isn’t there, go to the “Clients” form where you’ve got the “Link to Form” with 1:M set and tick “Show inverse relationship” to create the inverse field. Once the field is available you can look below the “Description” box to get the field ID.

The third line starts the loop through all of consultations where consultation is an individual record to work with. The fourth line then implements the test to see if the record ID of the invoiceClient (this is the client record linked to the invoice from the second line of the function) matches the record ID of the client record linked to the consultation. The fld-consultation_client_fieldId is expected to be the “Link From Form” field matching that in the consultation following the same pattern as we did with the invoice.

The next line attempts to add the consultation record to the current record’s fld-invoice_to_consultation_fieldId. This should be the field ID of the Link to Form field in the Invoice to the Consultations form.

The final line in the if statement toggles the checkbox referred to as fld-consultation_invoiced in the consultation record (again, replace with the field ID of the actual checkbox) and then as we work down through the rest of the script document.saveAllChanges() tells Tap Forms that we made changes it needs to save and the final line Import_Consultations() runs the script.

Now the script is going to be fragile to bad data so I’m going to make two slight changes to it that should make it a little less fragile, here’s the updated script:

function Import_Consultations() {
  let consultations = document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations");
  let invoiceClient = record.getFieldValue('fld-invoice_client_fieldId');
  if (!invoiceClient) {
    console.log('Client is not set for invoice.');
    return;
  }
  for (consultation of consultations) {
    let consultationClient = consultation.getFieldValue('fld-consultation_client_fieldId')
    if (!consultationClient) {
      console.log("WARNING: Consultation is missing client: " + consultation.getUrl());
      continue;
    }
    if (invoiceClient.getId() == consultationClient.getId()) {
      record.addRecordToField('fld-invoice_to_consultation_fieldId', consultation);
      consultation.setFieldValue('fld-consultation_invoiced', true);
    }
  }
  document.saveAllChanges();
}

Import_Consultations();

The first change is to check to see if invoiceClient is set and exits the script if it isn’t. Obviously if the client isn’t set then we can’t find the matching consultations. The second is to check on the consultation and flag a warning if the client isn’t set on it. When it logs, it logs the link to the record so if you run this outside of the script editor with the console open, you can click on the link to find the record.

Disclaimer: I haven’t tested any of this, just wrote it off the top of my head and it might have errors or changes but it should give you a general idea of how to approach the problem from the scripting perspective to help automate some of the action for you.

[GLS]

Thank you very much for your help!

I edited the script so that it has the proper IDs but I get the following error message in the console:

Invoicing: TypeError: document.getFormNamed("Consultations").getSearchName is not a function. (In 'document.getFormNamed("Consultations").getSearchName("Uninvoiced Consultations")', 'document.getFormNamed("Consultations").getSearchName' is undefined), line:(null)

I also tried with simple quotes ‘ ‘ without success.

[Daniel Leu]

The API function is called getSearchNamed() with a ‘d’ at the end.

[Sam Moffatt]

Yeah, that looks bugged, here’s something that should work a little better:

function Import_Consultations() {
  let consultations = document.getFormNamed("Consultations").getSearchNamed("Uninvoiced Consultations").getRecords();
  let invoiceClient = record.getFieldValue('fld-invoice_client_fieldId');
  for (consultation of consultations) {
    if (invoiceClient.getId() == consultation.getFieldValue('fld-consultation_client_fieldId').getId()) {
      record.addRecordToField(consultation, 'fld-invoice_to_consultation_fieldId');
      consultation.setFieldValue('fld-consultation_invoiced', true);
    }
  }
  document.saveAllChanges();
}

Import_Consultations();

Changes:

• Fix getSearchName to getSearchNamed
• Add getRecords() to the end of the same line to get the records
• Switch order of arguments on addRecordToField line

[GLS]

Thank you Sam and Daniel for your help.
Now it works great!!
Cheers
GLS

[GLS]

The code works perfectly, but the subtotal field in the main invoice form is not updated after populating the consultations to be billed. Is there a way to programmatically update this field? I could not find it in the documentation.

See “initial” as form status prior to populating consultations
See “populated” as form status after fetching the consultations to be billed – no subtotal
See “updated” after clicking in and out of subtotal or Total fields.

Thank you,

GLS

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

Does this happen on the default layout or just the custom layout?

If you click the refresh button at the bottom of the record does it also update the value properly?

[GLS]

It happens in the default view as well as in the custom layout view.

When refreshing the record the values are updated.

[kim kohen]

This completely solved a problem I was having. Many thanks.

[kim kohen]

Now that I have it working, there’s a couple of things I’m going to try to add.

1) A due date field which would be 30 days (or whatever the payment term) past the invoice date. Ideally when now() > due date the invoice record could change red (as per one of Sam’s videos). It would allow for easy follow up of overdue invoices.

2) I’ll add ‘Invoice number’ and ‘Payment Date’ fields to the Consultation form. When an invoice is created the invoice number would copy to the Consultation records. Once an Invoice is paid a script would copy the payment date to all the appropriate consultation records which would essentially ‘close’ the transaction.

[Author]

Posts