Tables – duplicate between forms

[David Oxtoby]

I did a search, but couldn’t find anything that offered a solution….

So, i have a table on one form that is a break down of the services a customer is receiving, (item, price, etc), and then on my invoice form which is a summary, i want to also show the same breakdown table, but without adding all the items in again from scratch. So…. is there a way when linking two forms, to show the table field from one on the other, it looks like there isn’t, therefore is a work-around….

have a script in the from that holds the table, to somehow extract the table content into a hidden text field that somehow formats the text, and then on the linked form just show that formated text field instead of a table?

or am i missing something really basic here…… many thanks

[Sam Moffatt]

It sounds like you’ve got two forms already and you want the same table on the two different forms. I’d suggest adding a third form that has the same structure as your table field and then use a “Link to Form” with the “JOIN” type. JOIN uses a field value that is common between the two forms to automatically build the links. In your case I think a customer ID would be a reasonable link value because you can put that on all three forms and link it together.

The Link to Form behaves similar to the table so you should be able to get the same effect in both places relying upon the same data.

Now I have a feeling that you might need this in thee future but a trick you can do with JOIN fields is to use a calculation field to JOIN on. You can use a calculation field to combine the values of two different fields together (via CONCAT) and use that value. In your example I can imagine a customer at some point stopping a service at which point they shouldn’t get invoiced for it in the future but you might want to include it in their printed invoice. You could add a checkbox or other flag that you check and use as a part of the composite key. The downside of this approach is that older invoices if reprinted will look different, so let’s think about that.

A JOIN field is great because it automatically updates values for you. A JOIN field is unfortunate because it automatically updates values for you. Thinking about “what services does the customer currently have active”, what you could do is when you generate an invoice you use a script to manipulation a normal “Link to Form” M:M field instead of a JOIN field. You would need to write a script to handle creating a link from the third form we created above to do it.

Give the JOIN field a try and see how it works out for you, if you feel we need to go down a little deeper on the scripted approach then I can give you a hand with that too.

[David Oxtoby]

Cheers for that Sam, what i ended up trying and seemed to work for what i needed, was create a script field that reads all the entries in my table and create a formatted text return value (not shown on the booking form), and then in the linked invoice form, i have another script field that pulls from the linked form that new formatted return value and shows that instead.

A bit of a faff inserting tabs and padding to to get the formatting looking ok, but works for what i needed.

see screen shots for idea of what it looks like… The Venue Booking shows the rooms being hired, that has been output into a hidden field, which the Invoice breakdown then shows in another scripted field.

Attachments:

You must be logged in to view attached files.

[T.L. Ford]

Probably should remove that second screenshot for PII.

[Sam Moffatt]

If you have script, why not just have it add new entries to a table in the other form?

[David Oxtoby]

Cheers Sam, i could unpack the formated text field back into a table i guess, but on reflection the text field allows me to squeeze a bit more info into the invoice for when it’s printed. But thx for that.

[Author]

Posts