Invoicing

[Vera Milosavich]

I posted this about an hour ago. I don’t see it in the forum or in my profile but when I tried reposting, it said it was a duplicate. I’m trying again and apologize if it’s a dupe.

***

I have been using FileMaker for over 20 years but it has finally gotten far too expensive for my now limited needs. I’m using an older version that is no longer compatible with the latest OS so I have to reboot every time I need to use it.

My most critical need is for invoicing. But before I spend days or weeks trying to recreate a similar solution, I would like to know if the type of invoice I need is even possible with TapForms. If it is, than any other report I’ll need should likewise be possible. I attached a schemata with a pared down set of fields and sample records showing what I need to accomplish.

Each month I import about a hundred (at minimum) records (downloaded from PayPal) into a single db. These records are for downloadable items I sell. Each month I need to create an invoice for commissions payable for the previous month’s sales. The commission rate varies by product so I store this info in a separate PRODUCTS table (is a table what you call a ‘form’ in TapForms?).

On my invoice, I need to show the name of each product and various subtotals for them: sold quantity (count), gross sales ($), commission rate (%), and commission total ($). I also need the typical aggregate invoice totals: subtotal, sales tax (if any), prepayment (if any), and grand total. Please see the attached for more detail.

Can this be done with TapForms? I tried analyzing the Invoice sample db but it didn’t help. Also…

Can TapForms filter (show or hide) records based on one or more fields matching a set of criteria?

Can I globally change the contents of a field (like clearing a checkbox, among other things) in a limited selection of records?

In Filemaker I could create a button where I could preset a different sort order for each report layout and not have the button print out on the actual report. Is there a similar feature in TapForms.

My apologies for this being so long.

Thank you!

Attachments:

You must be logged in to view attached files.

[Brendan]

Hello Vera,

What was it about the Invoices sample database document that didn’t help? The description of your requirements sounds awfully familiar to the Invoices sample. There’s a Clients form (yes, like a table), a Products form, and an Orders form, which is essentially your invoices.

You can filter records based on any fields that are in the parent form. I call these filters Saved Searches. You can even have different sort orders and field columns displayed for each Saved Search.

You can see on the sample screenshot attached that also shows the sub and grand totals.

There’s an advanced find and replace function in the Mac version which would let you do a global update. Plus there’s other tools for that sort of thing too such as the fill up/down and drag fill function, like you get in Excel or Numbers for copying the value of one cell down to others in the same column.

Hope that helps a bit.

Thanks!

Brendan

Attachments:

You must be logged in to view attached files.

[Vera Milosavich]

I couldn’t tell if the invoice example actually applies to my situation. Mine isn’t for invoicing buyers of the product so much as “reporting” the commission to the seller for the different products that were already sold. I was trying not to over-complicate my examples but I may have lost clarity in the attempt.

So on my report (which I refered to as an “invoice” for lack of a better word), I want to display subtotals for each of the products that were already sold that indicates how many of each product were sold during the month, the gross sales (dollars) and the total commission earned for each product (% rate and dollars). So the total number of line items appearing on the invoice could never exceed the number of records in the PRODUCTS table. See the tables with the grey headers on my schemata. Those are the line items. I think these would be a subset of the SALES table but they also cull some data from the PRODUCTS table/form. I would also need to perform calculations on the summarized values and also have those results appear in the line items:

• item count * selling price = gross sales
• gross sales * commission percentage rate = commission

The big question I have is whether I can run out summarized line items on my “invoice” as shown in those grey tables. I‘m pretty sure I can figure out the other parts but the summaries have me stumped. When I place a table “field” on my invoice, it displays all the items in my SALES table instead of summarizing each product.

If you say it CAN summarize within the line items as I described, I’m willing to puzzle it out some more. I just don’t want to waste the time if it isn’t even possible.

And if you can point me to a tutorial or example (perhaps other than invoice?) of how to create summaries like this, that may be helpful as well. Thank you!

[Vera Milosavich]

Brendan — I found your blog post on “Generate sub-totals for groups of records.” I think this is what I need, but I only want the group summaries to appear on the invoice (not all the items being summarized) and I want the product name to appear, too. How would I accomplish this?

[Brendan]

Hi Vera,

Tap Forms can’t display only the group summaries on the multi-column list view. It has to display the details in between. That’s because the summaries are derived by computing the totals from the item within each displayed section. At one point I looked into just displaying the summaries. Perhaps I should look into it again.

Thanks,

Brendan

[Vera Milosavich]

It’s actually on an invoice layout that I need item summaries. My original attachment might make that clearer than I can explain.

I do hope you can accommodate that! I desperately want to drop Filemaker — not because it isn’t an excellent program but because it’s way too expensive for a database — and especially for my needs.

[Vera Milosavich]

BTW: I did NOT mean to imply TapForms is NOT excellent. So far it is the best of the home- and small-business use database I’ve tried. But summaries are critical for me.

Maybe I’m approaching summaries in the wrong way…

I’m still trying to figure out how a lot of the fields and properties work. Is there a way to create a separate field that can display a sum based on matching criteria, like SUMIF() and SUMIFS() in Excel or Numbers? If you could implement those kinds of formulas, my problem might be solved!

Or is it possible to collapse the line items of a summary when displayed on another invoice — or even in the spreadsheet view because sometimes I really ONLY want to see the subtotals and not every transaction.

Or is there a way to copy/paste group summaries so I can paste them into an invoice? It would be clunky, but it’s better than typing it in.

Is any of this something a script can do NOW? If so, maybe I can get help with that down the line. I’m not excited to learn scripting, but if I can solve this one problem, I think I’ll be over the transition hump.

Thanks!

[Sam Moffatt]

Assuming you’ve set up your invoices with a link to form or table field in them for the individual line items, you can generate a script field that calculate what you need. The “Child records loop” snippet is a good place to start, you can use that to get the values of the child records and do what ever calculation you need.

I think one of the areas that Tap Forms is a little limited in is the reporting functionality with respect to nested elements. The custom layouts work well for single records and setting up simple labels but when it comes to building a single layout that could flow to multiple pages naturally based on child content, that just isn’t a thing. Getting something like that right in a generic sense is hard and would require building a new layout engine from scratch, it’s not the sort of thing I’ve seen outside of the expensive databasing products. I’ve spent a lot of quality time with page layout platforms and it can quickly get out of hand when you start to drill down into formatting, fitting items on pages, how you handle overflowing subcontent (e.g. should a child record be split apart or should it always be together?) and issues around odd/even page formatting. Very quickly printing things becomes…interesting.

[Vera Milosavich]

I can only imagine the complexity of layouts! My invoices never run longer than a page. Same for most reports because I mostly summaries so only a handful of items. If I need to see transactions, I prefer a searchable file. If that ever changes, it will be because I have LOTS of products and then maybe I can justify the cost of FileMaker again. But not with 3 products.

So are you saying there is no way to do multipage reports in TapForms or just that there are limitations with them?

Thanks!

[Brendan]

Hi Vera,

Ya, building a layout system that handles a fully customizable rows with grouping and summarizing is a ton of work. It is something I want to eventually do though.

Tap Forms can do multi-page reports by linking custom layouts together. But each custom layout can only be one page tall. But that does allow you to have lots of fields with complex layouts spread over multiple pages per record.

You could actually write a script that generated the summary data for you from your Link to Form (or Table) fields. You could even use a separate Table field to store the summary records and use that on your Invoice. So your Invoice could have the Table field (or Link to Form field) with all the details, plus a separate Table field to store the summary results.

You would have to use the Child Records Loop snippet as Sam suggested to loop through your child records accumulating a total for a field. I should really add the getTotalOfField() function to the record object so you could ask a Table field for a total of a specific sub-field.

[Vera Milosavich]

Can I get help with the Child Records Loop snippet — or whatever it takes to generate my invoices & summary reports? I have zero clue how to use javascript.

[Sam Moffatt]

Sure, share what you’ve got already and we can go from there. Probably put in the placeholders for your fields so we can work with that as well.

[Vera Milosavich]

Thank you, Sam. What I have is nothing as far as scripts go. I’ve tried using what seems to be the simplest snippet (get field value) but can’t get it to display the results:

var month_year_cart_item = record.getFieldValue('fld-66ac9ba074134a8598ae601cc72a1eb7');

As soon as I get a chance, I will post a description and diagram of the system I’m trying to develop. If from that information you can show me the script for creating summaries, and where it goes (and maybe tell me what each element of the script does?), I might be able to figure out some of the rest on my own.

Thanks again!

[Brendan]

Hi Vera,

Can you post your form template (.tff) file? That would give us a better idea of what you’ve got so far.

Thanks,

Brendan

[Vera Milosavich]

I don’t have much that works. It’s currently just a lot of experimenting. I’ll see wha I can do but I need to switch gears to a couple other projects for about a week.

But before I spend too much time and set myself up for possible frustration, can you give me some idea of how many records and fields might be too much for TapForms to handle — assuming disk space isn’t a factor?

[Sam Moffatt]

At least for me personally my largest by size TF document is around 21GB and has around 15k records across multiple forms with one form holding 11.5k records. Internally that’s all linked together and it’s actually an attachment heavy database to get to that document size.

My next biggest for me is also attachment heavy and weighs almost 10GB with around 5k records with my largest form holding 1941 records. Also has tonnes of internal links as well, it’s my purchase record log which is like the invoicing use case but more geared towards tracking purchases and purchase orders.

[Vera Milosavich]

That sounds promising. My current Filemaker db has 3 or 4 linked tables and 14k+ records in the largest table, accumulated over 5 years so roughly 2k new records are added each year. But that rate may increase in future years. It’s about 20mb now which is nonexistent compared to yours! I have no attachments but (combined amongst all tables) hundreds of fields, calculations, scripts (no JS!), and layouts — many of which I would eliminate if I retool for TapForms.

Aside from the upgrade cost of $540, my next biggest issue with FileMaker is that it has a crippled mobile app with no iCloud support nor import abilities. I can modify records and create new ones on the mobile app, but the hassle of transferring it back to my desktop to do monthly imports for invoicing isn’t worth it. So I basically use it as a read-only app which is better than nothing, but just barely.

If FM weren’t so expensive, I would upgrade and live with the lack of iCloud support and importing capabilities on mobile. But with those three whammies, I’m not happy. Even if I have to rebuild a stripped-down version and struggle with the learning curve, as long as TF can do the minimum of what I need, I think it’ll be more practical for me to switch over.

I understand TF for iOS has iCloud sync support. I don’t know if it can import CSV files, but as long as it can sync, I can live with that.

Thanks for the info on record capacity!

[Sam Moffatt]

Personally I rely on the P2P sync and CouchDB based sync approaches, there are quirks with iCloud from some of the posts on the forum.

There is a CSV import feature available on both iOS and the desktop. I’ve used it to import large numbers of records, I think one of the larger was around 2307 records, it was a data import of telemetry from my car. I might have even done it on my phone, it’s been a while. I ended up not taking that approach long term because it wasn’t a good fit for Tap Forms (second level granularity out of a car sensor). I was trying to visualise it inside TF using the map and whilst it sort of worked zoomed in, it didn’t work so well zoomed out (see the screenshots). That particular database I primarily use to track fuel receipts for my car so it’s a little chunky at 300MB but it also includes scanned copies of receipts going back to 2014.

Attachments:

You must be logged in to view attached files.

[Vera Milosavich]

When you say P2P sync, are you talking about Airdrop? And is NearBy (in sync preferences) the same as Airdop? I’m reluctant to overload myself with too much extra technical stuff so I may save the CouchDB option (looks like it has something to do with Apache server) for later — assuming I have problems with iCloud. Even if I have to force sync every time, that’s fine with me. That’s still a ton simpler that what I went through with FileMaker.

Awesome how you got the mapping feature to register the data from your car. Not sure what it’s depicting but the results are still fascinating.

Thanks!

[Sam Moffatt]

For P2P sync I mean the Nearby sync, sorry I should be clearer. The AirDrop feature is for shifting entire documents or bootstrapping a new device with an existing document (important for sync, you need to send your document to your new device and make sure they have the same document ID). The P2P stuff will just work so long as everything is on the same network, at least in my experience. I’ve even had it work over a VPN link but that’s not really something I’d rely upon. The P2P sync is also quicker than iCloud as well though obviously only works when you’re on the same network. Changes made offline will be replicated once the two devices can sync though if the same record is changed on two different devices, one of them will be overwritten by the other.

For my little use case, each of the little dots is a data point that was capture during my drive to work however when you zoom out the map clusters the points together which makes it not quite as useful for what I was trying to see.

[Vera Milosavich]

Do you know what determines how it chooses to cluster the data in the second image? I noticed the amounts in each cluster vary. I don’t mean to go off topic, I’m just curious.

RE syncing: Thank you for the clarification. Where does TF store the data file in iCloud? I turned on iCloud just to see but either it wasn’t there or I missed it. I thought it might create a TapForms folder at the top level but I didn’t see anything.

Also, would it be possible to have a TF data file on some cloud service or on my hosted web site server where I could share it with my associate? I don’t necessarily mean so we can both work on the same file — mainly so he can see the latest sales figures. The ideal would be a file that I can work on directly but that would be read-only for whoever I share it with.

[Brendan]

Hi Vera,

I actually don’t know what algorithm Apple uses to determine the clustering of location points. I do vary the size of the circle depending on the number of members of each annotation cluster though.

Here’s my algorithm for determining the size of the circles:

MKClusterAnnotation *clusterAnnotation = annotation;
NSArray *members = clusterAnnotation.memberAnnotations;
memberCount = members.count;
				
if (memberCount < 10) {
	imageHeightWidth = 30;
} else if (memberCount >= 10 && memberCount < 30) {
	imageHeightWidth = 35;
} else if (memberCount >= 30 && memberCount < 40) {
	imageHeightWidth = 40;
	pointSize = 13;
} else if (memberCount >= 40 && memberCount < 50) {
	imageHeightWidth = 45;
	pointSize = 13;
} else if (memberCount >= 50 && memberCount < 60) {
	imageHeightWidth = 50;
	pointSize = 14;
} else {
	imageHeightWidth = 55;
	pointSize = 16;
}

There won’t be a file stored in your iCloud Drive folder when you enable iCloud sync in Tap Forms. Tap Forms uses Apple’s CloudKit infrastructure which is like a big database in the cloud. Each change you make gets a record stored in the CloudKit database.

I also don’t recommend storing the .tapforms document file on any network shared volumes. This can cause database file corruption. It’s ok if you want to just transfer a file to another device, but always copy it out of iCloud Drive (or Dropbox or the file server, etc.) to your main hard drive before you open it.

For sharing the latest sales figures, you could just save a report as a PDF file and copy it to a web server somewhere they can download it.

If they need up-to-the-minute sales figures, you could sync the document to either IBM Cloudant or setup an Apache CouchDB sync service. But IBM Cloudant only allows up to 1 GB of free storage before they start charging you for syncing. Or you can always just send him backups of your document which he can restore from time to time if he wants access to the data in Tap Forms. There are user access controls in Tap Forms 5 for Mac which would allow you to prevent them from modifying the form and the records, essentially making the form read-only for them.

Thanks,

Brendan

[Vera Milosavich]

Is that algorithm javascript? If so, THAT’S the kind of scripting I understand — except maybe the first 3 lines. Whenever it gets into arrays or uses “.” or square brackets, I get lost. I’m not a programmer.

Yes, send PDFs now (with my FM solution), but I would like him to be able to see the info as up-to-date as I have it. Mine may be a few weeks old as I only import records at the start of the month (due to FM reboot nuisance). But if TF import and syncing from my iPad works well (that’s where I do most of my record-keeping), I might import more frequently. So thank you for that answer.

[Brendan]

The code above is Objective-C. But the if statement looks pretty much identical to how it would in JavaScript. Dots are just used to get the value of an object. For example, you’re Vera. That’s your first_name. Vera is a Person. So to get your name I can say person.first_name and I would be given the first name of you :)

Square brackets aren’t too scary either. They just help you to index into an array. You’ve seen an Excel spreadsheet I’m sure. They have row numbers on the left. In fact, the multi-column list view shows row numbers in Tap Forms. To get a value from an array you would just say people[10]. That would give you the 10th person from the people array :). Really not too scary if you think about it.

[Sam Moffatt]

With some work you could build a read only report with CouchDB but you’d be outside of Tap Forms at that point. I played with building such a thing a few times but never find enough time to dedicate to getting it finished and certainly not enough time to be customer usable. I’ve built stuff for my own personal use that exported from Tap Forms to external systems. The CouchDB/Cloudant support certainly opens a lot of capabilities though.

[Vera Milosavich]

Sam — I may give CouchDB a shot down the line, but I should probably stick to absolute necessities for starters….although it’s always good to know all the possibilities, so thank you!

[Vera Milosavich]

Thanks for the clarification, Brendan. Remembering is another problem! It’s like speaking English But with totally different grammar. I also get tripped up with the “punctuation.“ But maybe the light will come on after working at it for a while. I would hope.

[Author]

Posts