You're setting form.vars.employee = contraa.employee.fullname It should be set to the employee id, not the name. Results in the same problem as with contract name/id.
Should just be contraa.employee I believe... -Jim On Monday, September 26, 2016 at 10:23:15 AM UTC-5, Oasis Agano wrote: > > i changed it to form.vars.contract = contraa.id > but still getting the same error > > On Monday, September 26, 2016 at 5:39:41 PM UTC+3, Anthony wrote: >> >> In your model, db.payslip.contract is a reference field, but in your >> code, you attempt to assign a string value to it (form.vars.contract = >> contraa.contract_name). >> >> Anthony >> >> On Monday, September 26, 2016 at 10:04:30 AM UTC-4, Oasis Agano wrote: >>> >>> Models are here, if this way cant work can you suggest another way of doing >>> it >>> >>> MARITAL_SET = [ >>> T('Single'), >>> T('Married'), >>> T('Divorced'), >>> T('Widower') >>> >>> >>> ] >>> TIME_SET = [ >>> T('Part time'), >>> T('Full time'), >>> >>> >>> ] >>> >>> STATE_SET = [ >>> T('Trial'), >>> T('Employed'), >>> T('Fired'), >>> T('Retired'), >>> >>> >>> ] >>> #@auth.requires_login() >>> >>> db.define_table('employee', >>> Field('emp_photo', 'upload',label='Photo'), >>> Field('first_name','string',label='First Name'), >>> Field('last_name','string',label='Last Name'), >>> Field('fullname','string',readable='False',compute=lambda >>> r: r.first_name+' '+r.last_name), >>> Field('email','string'), >>> Field('phone','string'), >>> Field('marital_status', requires=IS_IN_SET(MARITAL_SET, >>> zero=T('--choose marital status--'))), >>> Field('number_of_children', 'string'), >>> Field('name_of_children','text'), >>> Field('address','text'), >>> Field('Manager', 'reference employee'), >>> #Field('date','datetime'), >>> Field('dob', 'datetime', label='Date of Birth'), >>> Field('doj', 'datetime', label='Hired Date'), >>> Field('ismanager', 'boolean', default=False,label='Is >>> Department Manager'), >>> Field('department', 'reference >>> department',requires=IS_EMPTY_OR(IS_IN_DB(db, "department.id", >>> '%(department_name)s'))), >>> Field('user', 'reference >>> auth_user',requires=IS_EMPTY_OR(IS_IN_DB(db, "auth_user.id", >>> '%(fullname)s'))), >>> Field('job', 'reference job_title'), >>> Field('Other', 'text'), >>> Field('state', requires=IS_IN_SET(STATE_SET, >>> zero=T('--choose employment status--'))), >>> auth.signature, >>> format='%(fullname)s' >>> >>> ) >>> db.define_table('job_title', >>> Field('job_name', 'string', label='Job Title'), >>> auth.signature, >>> format='%(job_name)s' >>> ) >>> #db = DAL(lazy_tables=True) >>> db.define_table('department', >>> Field('department_name', 'string', label='Department Name'), >>> Field('parent_dept', 'reference department'), >>> Field('Other', 'text'), >>> format='%(department_name)s' >>> ) >>> #db.employee.department.requires = IS_IN_DB(db, db.department.id, >>> '%(department_name)s') >>> >>> CONTRACTTYPE_SET = [ >>> T('Employee'), >>> T('Consultant'), >>> T('Freelance'), >>> T('Internship'), >>> >>> ] >>> >>> SALARYSTRUCTURE_SET = [ >>> T('Less than 30000'), >>> T('Between 30000 and 100000'), >>> T('Great than 100000') >>> >>> ] >>> >>> CONTRACTSTATE_SET = [ >>> T('Draft'), >>> T('Running'), >>> T('Expired/To Renew'), >>> T('Closed'), >>> T('Cancelled'), >>> >>> >>> ] >>> >>> >>> db.define_table('contract', >>> Field('employee', 'reference employee', required='true', >>> requires=IS_IN_DB(db, "employee.id", '%(fullname)s')), >>> # Field('date','datetime'), >>> Field('contract_name', 'string', label='Contract Title'), >>> Field('contract_type', requires=IS_IN_SET(CONTRACTTYPE_SET, >>> zero=T('--choose contract type--'))), >>> Field('salary_structure', >>> requires=IS_IN_SET(SALARYSTRUCTURE_SET, zero=T('--choose salary type--'))), >>> Field('job', 'reference job_title'), >>> Field('contract_scan', 'upload',label='Contract Upload'), >>> Field('date_trial_start', 'datetime', label='Starting Trial >>> Date'), >>> Field('date_trial_end', 'datetime', label='End Trial Date'), >>> Field('date_work_start', 'datetime', label='Starting >>> Working Date'), >>> Field('date_work_end', 'datetime', label='End of Contract'), >>> Field('wage', 'float'), >>> Field('rssb', 'boolean', default=True, label='Pay RSSB'), >>> Field('batch', 'boolean', default=True, label='Enable Batch >>> Payslip'), >>> Field('allowances', 'float'), >>> Field('visa_no', 'string', label='Visa No'), >>> Field('work_permit_no', 'string', label='Work Permit No'), >>> Field('visa_exp', 'datetime', label='Visa Expiry Date'), >>> Field('other','text'), >>> Field('state', requires=IS_IN_SET(CONTRACTSTATE_SET, >>> zero=T('--State--'))), >>> auth.signature >>> ) >>> >>> >>> >>> LOANSTATE_SET = [ >>> T('Draft'), >>> T('Confirmed'), >>> T('Refused'), >>> T('Paid'), >>> >>> >>> >>> ] >>> >>> LOANTYPE_SET = [ >>> T('Advance'), >>> T('Long Term'), >>> >>> ] >>> db.define_table('loans', >>> Field('loan_name', 'string', label='Loan Title'), >>> Field('employee', 'reference employee', required='true', >>> requires=IS_IN_DB(db, "employee.id", '%(fullname)s')), >>> Field('contract', 'reference contract', required='true', >>> requires=IS_IN_DB(db, "contract.id", >>> '%(contract_name)s')), >>> Field('loan_type', requires=IS_IN_SET(LOANTYPE_SET, >>> zero=T('--Type--'))), >>> Field('loan_amount', 'float',label='Loan Amount'), >>> Field('paid', 'float', label='Amount Paid',default='0.0'), >>> Field('balance', 'float', label='Loan Amount >>> Remaining',default='0.0',readable=False), >>> Field('date','datetime',label='Date >>> request',default=lambda:datetime.now()), >>> Field('date_start', 'datetime', label='Start of Payment'), >>> Field('no_of_months', 'integer',label='No of Months'), >>> Field('state', requires=IS_IN_SET(LOANSTATE_SET, >>> zero=T('--State--')),default='Draft'), >>> auth.signature >>> ) >>> >>> db.define_table('loanpayment', >>> Field('loanpayment_name', 'string', label='Payment Title'), >>> Field('employee', 'reference employee', required='true', >>> requires=IS_IN_DB(db, "employee.id", '%(fullname)s')), >>> Field('loan', 'reference loans', required='true', >>> requires=IS_IN_DB(db, "loans.id", '%(loan_name)s')), >>> Field('amount_payed', 'float', label='Loan Amount Payed'), >>> Field('date', 'datetime', label='Date of Payment'), >>> auth.signature >>> ) >>> >>> PAYSLIPSTATE_SET = [ >>> T('Draft'), >>> T('Confirmed'), >>> T('Closed'), >>> T('Cancelled'), >>> >>> >>> >>> ] >>> db.define_table('payslip', >>> Field('employee', 'reference employee', required='true', >>> requires=IS_IN_DB(db, "employee.id", '%(fullname)s')), >>> Field('contract', 'reference contract', required='true', >>> requires=IS_EMPTY_OR(IS_IN_DB(db, "contract.id", >>> '%(contract_name)s'))), >>> Field('payslip_name', 'string', label='Payslip Title'), >>> Field('gross', 'float',readable=False, writable=False), >>> Field('net', 'float',readable=False, writable=False), >>> Field('rssb_emp','float',readable=False, writable=False), >>> Field('rssb_comp','float',readable=False, writable=False), >>> Field('rssb_tot','float',readable=False, writable=False), >>> Field('paye','float',readable=False, writable=False), >>> #Field('loan','float',default='0'), >>> Field('loan', 'reference loanpayment',label='Loan >>> ',requires=IS_EMPTY_OR(IS_IN_DB(db, "loanpayment.id", >>> '%(loanpayment_name)s'))), >>> Field('date','datetime',default=lambda:datetime.now()), >>> Field('date_pay_start', 'datetime', label='Start of Payment >>> Period'), >>> Field('date_pay_end', 'datetime', label='End of Payment >>> Period'), >>> #Field('contract_type', >>> requires=IS_IN_SET(CONTRACTTYPE_SET, zero=T('--choose contract >>> type--')),readable=False, writable=False), >>> #Field('salary_structure', >>> requires=IS_IN_SET(SALARYSTRUCTURE_SET, zero=T('--choose salary >>> type--')),readable=False, writable=False), >>> #Field('job', 'reference job_title',readable=False, >>> writable=False), >>> Field('user', 'reference auth_user',label='Link to >>> User',requires=IS_EMPTY_OR(IS_IN_DB(db, "auth_user.id", '%(fullname)s'))), >>> Field('state', requires=IS_IN_SET(PAYSLIPSTATE_SET, >>> zero=T('--State--'))), >>> >>> auth.signature >>> ) >>> >>> >>> On Monday, September 26, 2016 at 5:00:14 PM UTC+3, Anthony wrote: >>>> >>>> Well, you still haven't shown your models. My guess is one of your >>>> fields is a reference field, which stores long int values representing the >>>> record ID of the referenced record, but you are attempting to insert a >>>> string value. >>>> >>>> Anyway, your approach won't work because SQLFORM only does a single >>>> insert, which happens after the onvalidation callback runs. So, only the >>>> last set of form.vars values assigned in the onvalidation for loop will >>>> end >>>> up being inserted in the database. >>>> >>>> Anthony >>>> >>>> On Monday, September 26, 2016 at 9:56:34 AM UTC-4, Oasis Agano wrote: >>>>> >>>>> TRACEBACK >>>>> >>>>> Traceback (most recent call last): >>>>> File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\restricted.py", >>>>> line 227, in restricted >>>>> exec ccode in environment >>>>> File >>>>> "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py" >>>>> >>>>> <http://127.0.0.1:8000/admin/default/edit/smartwork/controllers/default.py>, >>>>> line 708, in <module> >>>>> File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\globals.py", line >>>>> 417, in <lambda> >>>>> self._caller = lambda f: f() >>>>> File >>>>> "D:/lab/PycharmProjects/Smartwork/web2py/applications/smartwork/controllers/default.py" >>>>> >>>>> <http://127.0.0.1:8000/admin/default/edit/smartwork/controllers/default.py>, >>>>> line 300, in batch_payslip >>>>> if form.process(onvalidation=check_batch).accepted: >>>>> File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\html.py", line >>>>> 2298, in process >>>>> self.validate(**kwargs) >>>>> File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\html.py", line >>>>> 2236, in validate >>>>> if self.accepts(**kwargs): >>>>> File "D:\lab\PycharmProjects\Smartwork\web2py\gluon\sqlhtml.py", line >>>>> 1746, in accepts >>>>> self.vars.id = self.table.insert(**fields) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\objects.py", >>>>> line 726, in insert >>>>> ret = self._db._adapter.insert(self, self._listify(fields)) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", >>>>> line 739, in insert >>>>> query = self._insert(table,fields) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", >>>>> line 730, in _insert >>>>> values = ','.join(self.expand(v, f.type) for f, v in fields) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", >>>>> line 730, in <genexpr> >>>>> values = ','.join(self.expand(v, f.type) for f, v in fields) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", >>>>> line 962, in expand >>>>> rv = self.represent(expression, field_type) >>>>> File >>>>> "D:\lab\PycharmProjects\Smartwork\web2py\gluon\packages\dal\pydal\adapters\base.py", >>>>> line 1442, in represent >>>>> return str(long(obj)) >>>>> ValueError: invalid literal for long() with base 10: 'Jul Contract' >>>>> >>>>> >>>>> Note that this contract is the second in the database >>>>> >>>>> >>>>> Basically what im trying to do is create a loop(from a form) that will >>>>> create payslips for all running contracts >>>>> >>>>> >>>>> On Monday, September 26, 2016 at 4:21:56 PM UTC+3, Anthony wrote: >>>>>> >>>>>> Hard to say what the problem is without seeing the full traceback as >>>>>> well as your models. >>>>>> >>>>>> Also, what are you trying to do in the check_batch function? You are >>>>>> looping through some records and making assignments to form.vars, but >>>>>> only >>>>>> the final run of the loop will end up taking effect -- so what is the >>>>>> point >>>>>> of the loop? >>>>>> >>>>>> Anthony >>>>>> >>>>>> On Monday, September 26, 2016 at 8:29:35 AM UTC-4, Oasis Agano wrote: >>>>>>> >>>>>>> Greetings >>>>>>> im creating a payroll app and i need a to create a batch of payslips >>>>>>> >>>>>>> i want to create payslips for all contracts in the database within >>>>>>> the running state >>>>>>> the code >>>>>>> >>>>>>> def check_batch(form): >>>>>>> >>>>>>> contraaa = db(db.contract.state == >>>>>>> 'Running').select(db.contract.ALL) >>>>>>> for contraa in contraaa: >>>>>>> >>>>>>> if contraa.salary_structure == 'Less than 30000': >>>>>>> totgross=contraa.wage+contraa.allowances >>>>>>> >>>>>>> form.vars.employee=contraa.employee.fullname >>>>>>> form.vars.payslip_name=contraa.employee.fullname >>>>>>> form.vars.contract=contraa.contract_name >>>>>>> >>>>>>> form.vars.gross=totgross >>>>>>> form.vars.rssb_emp = totgross*0.03 >>>>>>> form.vars.rssb_comp = totgross*0.05 >>>>>>> form.vars.paye = 0 >>>>>>> totrssb=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> if type(form.vars.loan) !=int : >>>>>>> >>>>>>> form.vars.net = totgross-form.vars.rssb_emp >>>>>>> else: >>>>>>> payy=db(db.loanpayment.id == >>>>>>> form.vars.loan).select(db.loanpayment.ALL) >>>>>>> for pay in payy: >>>>>>> loanpay=int(pay.amount_payed) >>>>>>> form.vars.net = totgross-form.vars.rssb_emp-loanpay >>>>>>> >>>>>>> elif contraa.salary_structure == 'Between 30000 and 100000': >>>>>>> >>>>>>> form.vars.employee=contraa.employee.fullname >>>>>>> form.vars.payslip_name=contraa.employee.fullname >>>>>>> form.vars.contract=contraa.contract_name >>>>>>> >>>>>>> totgross=contraa.wage+contraa.allowances >>>>>>> form.vars.gross=totgross >>>>>>> form.vars.rssb_emp = totgross*0.03 >>>>>>> form.vars.rssb_comp = totgross*0.05 >>>>>>> varia =totgross-30000 >>>>>>> form.vars.paye = varia*0.2 >>>>>>> totrssb=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> if type(form.vars.loan) !=int : >>>>>>> form.vars.net = >>>>>>> totgross-form.vars.rssb_emp-form.vars.paye >>>>>>> else: >>>>>>> payy=db(db.loanpayment.id == >>>>>>> form.vars.loan).select(db.loanpayment.ALL) >>>>>>> for pay in payy: >>>>>>> loanpay=int(pay.amount_payed) >>>>>>> form.vars.net = >>>>>>> totgross-form.vars.rssb_emp-form.vars.paye-loanpay >>>>>>> >>>>>>> >>>>>>> #form = SQLFORM(db.payslip) >>>>>>> >>>>>>> >>>>>>> >>>>>>> elif contraa.salary_structure=='Great than 100000': >>>>>>> >>>>>>> form.vars.employee=contraa.employee >>>>>>> form.vars.payslip_name=contraa.employee >>>>>>> form.vars.contract=contraa.contract_name >>>>>>> >>>>>>> totgross=contraa.wage+contraa.allowances >>>>>>> form.vars.gross=totgross >>>>>>> form.vars.rssb_emp = totgross*0.03 >>>>>>> form.vars.rssb_comp = totgross*0.05 >>>>>>> varia2 =totgross-100000 >>>>>>> variah = varia2*0.3 >>>>>>> varia3 =70000*0.2 >>>>>>> form.vars.paye = variah+varia3 >>>>>>> totrssb=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> form.vars.rssb_tot=form.vars.rssb_emp+form.vars.rssb_comp >>>>>>> if type(form.vars.loan) !=int : >>>>>>> form.vars.net = >>>>>>> totgross-form.vars.rssb_emp-form.vars.paye >>>>>>> else: >>>>>>> payy=db(db.loanpayment.id == >>>>>>> form.vars.loan).select(db.loanpayment.ALL) >>>>>>> for pay in payy: >>>>>>> loanpay=int(pay.amount_payed) >>>>>>> form.vars.net = >>>>>>> totgross-form.vars.rssb_emp-form.vars.paye-loanpay >>>>>>> >>>>>>> #form = SQLFORM(db.payslip) >>>>>>> >>>>>>> else: >>>>>>> response.flash=T('Select a salary structure and contract') >>>>>>> >>>>>>> >>>>>>> def batch_payslip(): >>>>>>> db.payslip.gross.readonly =True >>>>>>> db.payslip.net.readable =False >>>>>>> db.payslip.rssb_emp.readable =False >>>>>>> #db.payslip.salary_structure.readable =False >>>>>>> db.payslip.rssb_comp.readable =False >>>>>>> db.payslip.paye.readable =False >>>>>>> db.payslip.employee.readable =False >>>>>>> db.payslip.employee.writable =False >>>>>>> db.payslip.contract.readable =False >>>>>>> db.payslip.contract.writable =False >>>>>>> db.payslip.payslip_name.readable =False >>>>>>> db.payslip.payslip_name.writable =False >>>>>>> db.payslip.loan.readable =False >>>>>>> db.payslip.loan.writable =False >>>>>>> db.payslip.user.readable =False >>>>>>> db.payslip.user.writable =False >>>>>>> form = SQLFORM(db.payslip) >>>>>>> >>>>>>> if form.process(onvalidation=check_batch).accepted: >>>>>>> response.flash=T('Payslip Added') >>>>>>> return dict(form=form) >>>>>>> >>>>>>> >>>>>>> im getting this error >>>>>>> >>>>>>> ValueError: invalid literal for long() with base 10: 'with a contract >>>>>>> name' >>>>>>> >>>>>>> -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.