Questions about your Knack app? Let's Chat. https://www.knackbuilders.com. Invoices are a common function within Knack database apps. Here I show you how the Objects and data should be structured to accomplish this.
Get a free Knack trial account here: https://www.knack.com/r/you-tube
If you are a fan of building great apps with Knack, please click the Subscribe button to join my channel, and ring the bell to receive notices on all my new instructional videos.
Thanks!
Full Transcript
hey folks dave parish here at knack
builders uh
today's video
i'm going to talk about
how to make invoices in a knack app
it takes a specific uh
object or data structure to do those
correctly
and i'm just going to go over the
process of how to do it and actually the
structure for
invoices you have an invoice
to which you can add many items so one
typical one-to-many type of structure
that can be applied in a lot of things
uh i just did another app
with uh
classes
uh in a school and very similar
structure you have a class
called something
certain dates whatever
uh that would sort of be like the
invoice and then to that class you're
going to add students
um
and the students would sort of be like
the products you're selling so again one
to many this is useful uh first time
i was doing it i didn't really know what
i was doing
uh and
i finally figured it out
and it really helps uh and invoices are
real
invoices or
orders pos whatever they're all the same
uh very useful i'll be back in a minute
thanks
[Music]
okay i'm back uh dave parrish at knack
builders going to be
talking about um
how to make invoices
in a knack app let's jump in
that is let's call them an app
okay
um
i'm gonna go through
the data structure the tables you need
um
and how they're related to one another
uh first clients this would be clients
customers or whatever you're gonna have
a list of those
typical stuff their name
don't number this one's an easy
every order is going to have
client
and then each client
can create an order or an invoice
um
so you're going to grab
what i call the boilerplate or native
stuff about
the
order or the invoice
which
actually has
not much to do with what they're
actually purchasing that's going to be
different you're just going to want to
know okay the client you know typical
step an order number invoice number
uh
client name the date of it
uh
in this case we're actually tracking
inventory too i'll touch on that but
that's a whole nother topic
products are coming from certain
locations
in this case i do an account
connection which ends up grabbing
whoever created the inward so you have
that
this is counting the number of
things they purchased it's also summing
each thing that is on the invoice so you
get a total
uh it's basic stuff okay
and then
i'm gonna jump down to this one items
these would be the products you're
selling this is
important and i'm gonna spend a little
bit of time on the difference
of
of what a product is
and what when that product
appears on an invoice those are actually
two different things
uh so a product would be typical you got
a product number description
what its
cost is
uh that is
you know largely in this case
these products they're distributing
different sort of hygiene products so we
got
you know the name of the product item
number
we can have an active or inactive status
on these so when you're choosing what
products to put
on an invoice you could filter it by
only once they're you're currently
selling because you don't want to get
rid of those what it cost is
that type of
okay
now here's the important one
i call in out items
because we're actually tracking
inventory on all this too some apps
would just create an inventory or
an invoice
for what was sold and to be charged you
can even have them pay for it with mac
uh e-commerce function
uh in this case we're tracking inventory
too
and um
so
when i do inventory i usually have one
main table that's looking okay what's
coming in meaning we're resupplying
what's coming out
we're selling
um
and also when you're doing inventory you
want to give them a ability to do a
manual
update to do a physical inventory it's
awful what the system says for whatever
reason they
adjust that on their own
but when
you're doing an invoice
again you're going to be
have a client you're gonna say i'll show
you the interface but you're gonna say
okay let's create a new order for this
client
date it's gonna capture who the client
is
um
whatever
boilerplate information you want about
it then you're going to add
items to it
but what
you're actually not doing you're not
actually adding
an item
in terms of the data here to it
you're adding an instance of that item
um and i'll show you how that works
because
an item is what it is it's a the name
part number
current value
you know those are some of the main
things
but when
you're applying it to an order there's
other variable information
um
one
could be quantity
uh so you need to be able to capture
that
you may have a discount structure you
have an item that has a current value
but maybe
your sales person has the ability to
adjust that
well that's going to be
a variable for an instance of that
product
so
it's important to understand
that concept an item is just denied when
you're selling it it is
an instance of you selling that item and
adding it to the
to the uh
to the invoice or order let's see what
we have here
so
obviously client order
a new order will have a connection to
the client
that's the main one
um
items don't necessarily need an instant
to the client because they're separate
but an instance of that item going out
these are the line items
on the invoice
we're gonna
it's gonna be automatically connected to
the order we're gonna grab who the
client is
in this case we're doing this
inventory
volume by location um
and then an instance of this item
is
um
you're gonna
you're gonna in the instance or the in
out you're gonna connect that to
the items so in this case
i'll get into this if you're selling
some particular product bathroom cleaner
product number whatever
you're gonna select that and then you're
gonna put the volume or the
the quantity and other type stuff
so
absorb that let's go over the pages side
see how this actually is working
i'm on the builder but you'll get the
deal so in this case
you'd log in
you'd go to a client
you look at that client and then you'd
say hey okay here's
here's
all this clients orders so we're going
to create a new order for that client
you're going to add in
see what this looks for
um
again date comments
and the location
very basic stuff now let's go to the
details of that
um
here's the order
you know all the boilerplate stuff up
top let's add a new item in this case
we're going to
this connects back to the items of the
list of products you have
and you're going to select which one
you're going to put in the quantity and
put in
whatever comments you have and let's see
what's driving some of these rules
record rules are important here
so when i add that
item to the particular
um
invoice i want to capture a bunch of
other information about it
who i want to native in this uh
in out or
line item to the order i want to grab
who the client is in this case the
location
the date
and
is it
for inventory purchases is it coming in
or out uh and this time it's outgoing
and it's an order
and this one's important too i've seen
the uh apps
where the cost of the line item
they
create an equation
that links directly to the
cost
or the price of
the item itself
um
now that seems like it might work let's
just whatever that item costs let's make
the line item of that item when it sold
the cost of that item well that really
doesn't work um
that can be a variable because over time
it is most likely
that value will change so you do not
want to link it directly to it
because
how that would work in practice is over
time if an item changed
the invoice from before the change
would its value would change too so you
go look up to an old
invoice
uh you've updated pricing
that invoice would be wrong it won't
reflect what's actually sold
what you'd rather do is you're going to
have that field it's the
value currency field
and you're going to say hey when i
create this line item i want to go look
at
the current cost
of that product
and i want to grab it
and insert it as the value i don't want
to link it i just want to go get it put
it in there
so if the price changed the next day
that value would be
the same it would not change that's an
important
part
part to look at here
um
that's largely it and i'm gonna recap
that
a little bit here you're gonna have your
client
that's easy
a client has an order
basic stuff boilerplate order number
uh invoice number um
date
you're going to have a list of your
products now
sometimes there isn't a list of products
you're selling one-off things
in that case you wouldn't need a list
every line item would sort of be unique
but most time you're going to be drawing
from some pool of products you're
selling
and then each product each time you sell
one of those products
you're
not actually selling the item itself
you technically are selling an instance
of that item and you're capturing
variable information about it uh
price
um
and quantity are the large one so that's
it folks hope that was helpful uh
appreciate your time thanks
hey i know i said i just was finished
with that but after looking at the video
i wanted to clarify one thing that's the
direction of these connections
uh
client
uh or actually client order and client
and client order are related but the
connection is right here
then you're usually the connection is um
in the direction of the child
so a client in a client order is the
child
client orders client to the child that
means the connection will reside in the
child so so when you're looking at the
records
i'm gonna have a whole other video on
this uh
the name of the client
is
appears in the child which is the client
order
but that's a pretty easy one
and this would be
similar in this direction
in out items the instances of an item is
a child
of items so
so we look over here we have
um
items
we see
that it's connected to the in out items
but the connection resides in the in app
right here
so it's one to many one item can have
many instances of it being sold let's go
to that
find it here item right here
these all look the same
um
each
instance of this item connects with one
item
and each item
connects with many make sure they're set
up like that
what that allows you is you can look at
any item
and you say you had a list of all your
items and you want to click down and see
the details it's going to show you all
the times it was sold
and you do that in the other direction
too you can look i mean that same thing
happened you look at the order obviously
you see all the items on it you can also
look at the client and see all the
everything they bought not just
all their
orders
but rather every single item directly
because they're all going to be
connected back and forth i just wanted
to clarify that
um
when you do your connection you're going
to initiate the connection from the
child
the one to many if
one again i always think of one to many
one many one client has many orders
one item as many times it can be sold
initiate it from the child swing and
which more specifically means that's
where you're going to see the name of
the connection
go back to here
here's the item the instance of it being
sold you see it in
the child record which is there it's
going to lead back to the parent
that's it hopefully that helped thanks