Dynamic invoices (.docx) with subtable - problem

Johanna_Schmidt_Fotograf
3 wk ago
25replies
Answered

Hi!

I have just started playing around with dynamic printing, as the built-in solution is - as we all know - less than ideal and finally has reached the end of life for me.

All is working well - except the implementation of a different table into my invoice. In my invoice, I have a table called "summary" - which pulls data from a different table called "article"

There, I have two columns "product" and "price" If I understand the Ninox documentation correctly, the correct syntax should be

{d.summary[i].article.product}

to pull the product in my invoice. But that doesn't work, even though {d.summary[i].finalprice}, which pulls the final price (price * amount) directly from the "summary" table, works perfectly.

So, I guess my syntax is incorrect - could you point me in the correct direction where I might have gone wrong?

Thanks.

Johanna

- Fred
- 3 wk ago
- Reported - view
Try:

{d.summary.article[i].product} {d.summary.article[i+1]}
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Thanks, but same result. Empty lines in the PDF where the
  
  {d.Zusammenfassung.Artikel[i].Leistung}
  {d.Zusammenfassung.Artikel[i+1]}
  
  are in the docx. And yes, those are my actual field names - I just translated them into English for here to make it easier to understand...
  
  Johanna
- Fred
- 3 wk ago
- Reported - view
Oops, I was wrong. Here is what works for me when I want to use data from a reference field inside a reference field.

{d.ProductionItem[i].Materials.Name} {d.ProductionItem[i+1].Materials.Name}

I'm starting in the Production table. ProductionItem is a many to many table linking Production and Materials. Production (1) > (M) ProductionItem (M) < (1) Materials.

This is what I see:

mat5, mat1, mat7 are the names of the material records.

Are your tables setup up the same? Can you post a sample DB?
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Attached are two screenshots of my database. I don't really know how to post a sample DB... sorry about that.
  
  Image 1 shows you the table named "Artikel" where I am pulling the data from. Two columns, called "Leistung" and "Preis", nothing fancy.
  
  Image 2 shows the table named "Hochzeitspaare" that is pulling the data from "Artikel" in a 1:N relationship table called "Zusammenfassung."
  
  In my invoice, I need to pull the title of my service "Leistung" from the "Zusammenfassung" table (and therefore indirectly from the "Artikel" table to print in my invoice.
  
  Thank you for all your help!
  
  Johanna
- Fred
  
  3 wk ago
  
  Reported - view
  Are you using the MacOS app? or the public cloud? If the cloud then which level did you subscribe to?
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  I've got the starter subscription and use the MacOS app
- Fred
  
  3 wk ago
  
  Reported - view
  In the MacOS app:
  
  open the DB
  
  click on Table menu and select Export data
  
  click on Ninox (third option after CSV and Excel)
  
  click on Save as button
  
  select a location and a name the click on Save
  
  If the data is safe then you can upload the DB to the forum.
  
  Otherwise you will have upload it to your MacOS app locally then make it a safe DB then follow steps 1 - 5 again.
  
  I find that creating JSON datasets makes things easier for Carbone, but if you are not ready to jump into that we can work on making it work with Ninox field names.
  
  Just be aware that Carbone can't handle any spaces in field names so if you have any that you think you will use then you will have to use JSON to make the fieldnames kosher for Carbone.
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  The database has over 350MB - I'll try to make a duplicate, kill most of the content and upload it...
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Hi, Fred! Here is the database. In the "Hochzeitspaare" table, I deleted every entry except for one, which I filled with dummy data for privacy reasons. The other tables are complete and original.
  
  Johanna DB small.ninox
- UweG
- 3 wk ago
- Reported - view
On the one hand, I would think about whether the starter package with its limitations is the right subscription for a business application, especially since e-invoicing will sooner or later become mandatory for business in Europe and the starter subscription is not supported by Ninox with e-invoicing.
On the other hand, I would also make the data available via custom JSON for the use of dynamic printing (Carbone).
In the long term, this will cause fewer problems than with the simplified version: {d.Fieldname}

See also: https://forum.ninox.de/t/g9yxpzm/bekannte-komplikationen-beim-dynamischen-druck-dynamic-print-und-wie-man-sie-umgehen-kann

If the print template is in the 'Hochzeitspaare' table, the following table variable should work:
1.Zeile: Headings
2.Zeile,Zelle1: {d.Hochzeitspaare.Zusammenfassung[i].Leistung}
2.Zeile,Zelle2: {d.Hochzeitspaare.Zusammenfassung[i].Anzahl} etc.
.
.
.
3.Zeile,Zelle1: {d.Hochzeitspaare.Zusammenfassung[i+1].Leistung}

I would still prefer the custom JSON variant.
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Danke Dir! Den Link schaue ich mir mal in Ruhe an!
- Fred
- 3 wk ago
- Reported - view
Ok, try the attached docx file.

You tried:

{d.Zusammenfassung.Artikel[i].Leistung}

but it should be:

{d.Zusammenfassung[i].Produkt.Leistung}

In the Rechnungsposition Hochzeit table the reference field to Artikel is called Produkt so you have to use that name.

Attached is also the output.
johanna report-ZUu0pbYmbjm7S088
- Fred
- 3 wk ago
- Reported - view
I also noticed that you have many field names with spaces in them. That means you will need to get comfortable with creating JSON datasets.

You can practice by creating a new formula field and put this in it:

let t := this; t.{ brautvorname: 'Braut Vorname', brautnachname: 'Braut Nachname' }

So creating a JSON is as simple as putting your output inside those curly brackets. Keys end with a colon and the line ends with a simple comma NOT a Ninox semi-colon.

Good luck.
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Thank you so much! That did it!
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Having played around and finally wrapping my head around all of this, I (for now) only have one question left: Is there a way to order the output in descending order by price? Inside Ninox, it's simple, but as far as my output goes, I think it is ordered either randomly or by the order that I put them in my Zusammenfassung table...
- Fred
  
  3 wk ago
  
  Reported - view
  Sadly there isn't if you DO NOT use custom JSON.
  
  When you create your JSON to iterate through Zusammenfassung you need to do something like:
  
  let t := this; t.{ brautVorname: 'Braut Vorname', zusammenFassung: for zF in Zusammenfassung do { prod: zF.Produkt, gesamtpreis: zF.Gesamtpreis, gesamDisplay: text(zF.Gesamtpreis) } end order by -number(gesamtpreis) }
  
  You can use the order by command at the end of a JSON to tell Ninox how to sort. To do a reverse sort you just put a negative in front of a number.
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Thank you! I don't think I quite understand it (yet), but I will get there soon enough :-)
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Final question, after playing around with it a while:
  
  I have now reassigned all my field names using Custom JSON. Also, I have included your code:
  
  {
     BrautVorname: 'Braut Vorname',
     BrautNachname: 'Braut Nachname',
     BrauteMail: 'Braut eMail',
     BraeutigamVorname: 'Bräutigam Vorname',
     BraeutigamNachname: 'Bräutigam Nachname',
     BraeutigameMail: 'Bräutigam eMail',
     Ehename: Ehename,
     Telefon: Telefon,
     Adresse: Adresse,
     DatumAnfrage: 'Datum der Anfrage',
     Hochzeitsdatum: Termin,
     Wochentag: Wochentag,
     Startzeit: Startzeit,
     Endzeit: Endzeit,
     Dauer: Dauer,
     OrtTrauung: 'Ort der Trauung'.Location,
     OrtFeier: 'Ort der Feier'.Location,
     AnzahlGaeste: 'Anzahl Gäste',
     zusammenFassung: for zF in Zusammenfassung do
         {
             prod: zF.Produkt,
             gesamtpreis: zF.Gesamtpreis,
             gesamDisplay: text(zF.Gesamtpreis)
         }
     end order by -number(gesamtpreis)
  }
  
  My reassigned field names work beautifully using {d.} - but I just can't get your code to work. I just can't figure out which of your newly declared variables to put in place of my original {d.Zusammenfassung[i].Produkt.Leistung}.
- Fred
  
  3 wk ago
  
  Reported - view
  here is what it should look like. You just use the new names exactly as you typed it.
  
  johanna2
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  Thank you! That works, but now spits out a number instead of the text.
- Fred
  
  3 wk ago
  
  Reported - view
  Produkt is a reference field, so you can pull any fields from it. Try adding the field name you need.
- Johanna_Schmidt_Fotograf
  
  3 wk ago
  
  Reported - view
  That did it. I finally start getting the hang of it. JSON really is a somewhat non-intuitive language...
- Fred
  
  3 wk ago
  
  Reported - view
  JSON is just a way of organizing data, kind of like creating a table on the fly. You have a key or field names (the name to the left of the colon) and the value or data (the data to the right of the colon).
- Ninox partner
  
  RoSoft_Steven.1
  
  2 wk ago
  
  Reported - view
  If I may suggest something and just FYI, it is better not to use loops in the construction of the JSON. Especially when this formula is used in a global function and called with API. Rather, I suggest this code, which is much faster...
  
  let t := this; t.{ brautVorname: 'Braut Vorname', zusammenFassung: Zusammenfassung.{ prod: Produkt, gesamtpreis: Gesamtpreis, gesamDisplay: text(Gesamtpreis) } order by -number(gesamtpreis) }
- Fred
  
  2 wk ago
  
  Reported - view
  I thought I've tried that and was unsuccessful, but if you vouch that it works then I agree that this the way to go.

Content aside

Status Answered
2 wk agoLast active
25Replies
113Views
4 Following

Dynamic invoices (.docx) with subtable - problem

25 replies

Content aside

Overview