Auto-populate pricing from another table?
Ok Ninox wizards, now I'm trying to auto-populate some pricing and job details.
Here are the top level details:
- Main table is 'Job Status Log' which has 100s of job entries that are connected to my...
- 'Project Details' table, which is where I put in the details of each job (info, pricing, tax calculated, etc)
- This is not a 'sub' table yet, but it could be if needed (have to figure out how to do that, haha)
- Screen shot of setup attached
Ideally, I'd love to be able to:
- Type in the 'Details' text field and have it auto populate details that are sourced from a 'pricing' table
- For example, if I were to type in 'Brand Gui...' it would populate 'Brand Guide, Per Page' and give me an option to choose from x3 tiers of pricing, then based on the tier I pick it would auto populate the price that again is dictated in the 'pricing' table I've created
- The thought behind this is because:
- Most of my jobs are serviced based, and priced per-job (not hourly) and based on client type.
- For example, I wouldn't charge a small mom-and-pop type business the same thing that I would charge another, larger client where the design I create would get more visibility and use
- Ideally, I'd also like to have the ability to modify the 'details' and 'price' fields depending on if I need to add additional details or a discount/markup
Maybe this is a 'pie in the sky' type thing, but as a whole it would be great to standardize my pricing a bit. Any ideas on how to accomplish or make this easier please let me know. Still fairly basic but learning lots...
Thank you!
14 replies
-
What you need is a reference field in Project Details that points to Job Pricing. You can call it whatever you want in Project Details, but let us leave it as Job Pricing for now.
For every new record in Project Detail you will click on Job Pricing and at the top will be a search box and you can start typing in "Brand Gui..." and it will bring up any record with any data with "Brand Gui". Then you can select the record you want. You can modify the columns that are shown in the pop up.
You can then create three buttons in Project Detail below Job Pricing that say Low, Mid, High and put the following code in each, assuming that Project Detail.Quote is a number field:
Quote := 'Job Pricing'.Low
Replace "Low" with the appropriate field name for Mid and High.
-
Have a look at a file of an unfinished DB test I was trying to develop some time ago
Look at quote lines. This looks up a product from another table and picks up the pricing. I was also experimenting with qty breaks etc and batch updating the prices. In this example there is no main header quote body as I was only concerned with the testing of the quote lines section.
I went down another direction in the end. So this project was parked and not touched any further! But might/might not be of some use. (even if not how to do it )
One of the things I personally don't like, which I have seen various examples on this forum, is putting prices and or tax codes directly into a script embedded within the table form. I see these as quick fixes but when you want to make changes to tax rates your effectively diving back into design and could potentially fall foul to unintentionally altering previos stored records. I prefer separate tables for products / prices and tax codes.
-
said:
However, none of the other 'Job Pricing' items seem to work. Any ideas?You originally had the code written as:
if 'Job Pricing N1' = true then 'Unit Price' := 'Job Pricing N1'.Low end
I guess technically it should work for all records if it worked for the first one. But if you think about it the field is not a yes/no field so I'm not sure what Ninox is evaluating as "true".
I changed all the buttons to:
if 'Job Pricing N1' != null then
and that seemed to do it. Now we are telling Ninox to only do the copy when the field is NOT null. Which is what you want, only when there is data in the field do you want to copy the data.
-
said:
Also, I'd love to have the Job Pricing item I select (in this case, Logo Design) auto populate in the 'Details' field above it. I will need to be able to add more text to that section though, so if that's not possible then no worries.Just add it to the buttons:
if 'Job Pricing N1' != null then 'Unit Price' := 'Job Pricing N1'.Low; Details := 'Job Pricing N1'.Item end
Now this will delete whatever is already in Details and put in what is in Item. If you want to add what is in Item to the front of what ever is there you have to add:
if 'Job Pricing N1' != null then 'Unit Price' := 'Job Pricing N1'.Low; let extra := if Details = null then "" else " - " end; Details := 'Job Pricing N1'.Item + extra + Details end
But this will add it every time you press one of the buttons, so I guess you need to figure out if it works better as a button or Trigger after update for 'Job Pricing N1'. If you do it as a trigger then you can remove the if statement and the Unit Price part.
So what line 3 does is depending on if there is already data in Details, it will add a dash so you can visually separate the data and if there is isn't data then it doesn't add anything.
-
Ah Taylor
Thank for the sample db. I can the pricing route you are taking. I guess effectively most of your pricing is service based ! cos its your time as opposed to a product and the low/med/high pricing structure will clearly work for you.
Just a small comment ( and you may have already dealt with this ) but you have small table error in project details - The date field - Interim Invoice Date is highlighted to show the error and this is also confirmed as a warning triangle on the table when you go into table edit mode.
When you open the field up you can see this.
D1 means you have lost the reference to something ie maybe you deleted the other field etc.
so your true condition won't get triggered until corrected.
-
So what line 3 does is depending on if there is already data in Details, it will add a dash so you can visually separate the data and if there is isn't data then it doesn't add anything.
What if you price "low" and then changed your mind?. Would it not be better to have 'Details' field clear when you select low/med or high ?
-
Taylor Hanson What I was referring to is this
In you project I select a job type and also select low price option. The details field has been filled in.
I then think - oops that should have been set for Med prices - so i click the med price option.
now look at details field - its doubling up the text (easy to fix)
Okay re your text blocks where do these exist
are they in emails, excel, word etc;
cos you could always pull them into a temp table and link them, or work from there.
Content aside
- 1 yr agoLast active
- 14Replies
- 185Views
-
3
Following