Auto-populate pricing from another table?

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.

Taylor Hanson

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. 

Taylor Hanson 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.

Taylor Hanson 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.

Taylor Hanson 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 ?